CODEWITHSUNDEEP
excelexcel-formulascheduled-tasksweekdayweekend
Maxine Carter
Maxine Carter

Reputation: 1

Putting a years schedule together (based on Mthly waves) and need to know the formula that if a task falls on a weekend, it moves to weekday

I'm putting a schedule together (1 year - 12 month waves) - I want to autopopulate as much as populate - therefore, I'll have a few key dates and then will add formula to the remaining.. i.e. +1 day, +3 days etc... However, I need to know the formula that if the formula throws up a Saturday or Sunday, I need to add a day or two so the day for the task is a working day

Haven't had any luck

Upvotes: 0

Views: 171

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27448

Another alternative way of accomplishing the schedule is using WORKDAY() , NETWORKDAYS() & SEQUENCE() Functions:

In cell B1 we have the Start_Date as 1/1/2023 while in cell B2 the End_Date as 31/12/23 for the year 2023 respectively, using cell references will make it dynamic as well!

The use the following formula in some where in cell D1 as

=WORKDAY(B1,SEQUENCE(NETWORKDAYS(B1,B2)))
  • The NETWORKDAYS() Function returns the number of whole workdays between two dates i.e. Start_Date and End_Date which 260 for this year 2023.
=NETWORKDAYS(B1,B2)
  • Next wrap it within a SEQUENCE() function to return a sequence of numbers, starts from 1 and ends in 260.
=SEQUENCE(NETWORKDAYS(B1,B2))
  • Lastly, taking the WORKDAY() function to return the serial number of the dates after the number of specified workdays.
=WORKDAY(B1,SEQUENCE(NETWORKDAYS(B1,B2)))

enter image description here

• Formula used in .Gif to show:

=LET(
     α,WORKDAY(B1,SEQUENCE(NETWORKDAYS(B1,B2))),
     HSTACK(TEXT(α,"ddd"),α))

Upvotes: 0

P.b
P.b

Reputation: 11653

You could achieve this by 2 sequences: =TOCOL(SEQUENCE(52,,44928,7)+SEQUENCE(,5,0))

The first sequence creates 52 numbers (number of weeks) numbers starting from 44928 in steps of 7 between each value. (44928 is Excel's date representation of Monday January 2nd 2023. You could also refer to a cell that holds the start date, as long as that's a Monday). This sequence is vertical.

The second adds a horizontal sequence of 5 (days you want to show) to the first sequence.

Since your first sequence is vertical and this is horizontal, the 2nd sequence will be added to each row of the 1st sequence. This creates a 2D array. We can use TOCOL to flatten the 2D array to 1D vertically, or TOROW to flatten horizontally.

Upvotes: 1

Related Questions