Need help with Dates Incremented by .25 days, and excluding weekends.

[HighlandRanger]

I am trying to create a formula that increments by .25, .5, .75, 1 so that I can create a Gantt Chart that accounts for those increments. I need to be able to skip weekends. Like this:

2/22/21 0:00
2/22/21 6:00
2/22/21 12:00
2/22/21 18:00
2/23/21 0:00
2/23/21 6:00
2/23/21 12:00
2/23/21 18:00
2/24/21 0:00
2/24/21 6:00
2/24/21 12:00
2/24/21 18:00
2/25/21 0:00
2/25/21 6:00
2/25/21 12:00
2/25/21 18:00
2/26/21 0:00
2/26/21 6:00
2/26/21 12:00
2/26/21 18:00
3/1/21 0:00 Skipped over 2/27 and 2/28


Basically I need to be able to do this calculation using a START DATE, amount of EFFORT and calculate the END DATE from those.

Any Help?

 

[Macropod]

Cross-posted at: Need help with Dates Incremented by .25 days, and excluding weekends.
Please read Mr Excel's policy on Cross-Posting in rule 13: Message Board Rules

 

[HighlandRanger]

I have posted this question on another forum: Need help with Dates Incremented by .25 days, and excluding weekends.

 

[jdellasala]

If you have Office 365 with the FILTER and SEQUENCE functions, this formula will provide an array of 30 days starting today in 6 hour increments (.25 of a day):

=FILTER(SEQUENCE(30*4,,DATEVALUE("2/22/2021"),0.25),(WEEKDAY(SEQUENCE(30*4,,DATEVALUE("2/22/2021"),0.25))>1)*(WEEKDAY(SEQUENCE(30*4,,DATEVALUE("2/22/2021"),0.25))<7))

You can replace 30 in the formula with the number of calendar days you want (the formula above provides 29.75 calendar days, 22 workdays, and 88 quarter day slots), and provide a cell with a date to replace DATEVALUE("2/22/2021"). I only used it so the complete formula would stand alone.

If the number of days you wanted to use was in cell A1, and the start day was in cell B1, the formula would be:

=FILTER(SEQUENCE([B]A1[/B]*4,,[B]B1[/B],0.25),(WEEKDAY(SEQUENCE([B]A1[/B]*4,,[B]B1[/B],0.25))>1)*(WEEKDAY(SEQUENCE([B]A1[/B]*4,,[B]B1[/B],0.25))<7))

Both of those formulas will spill down rows. If you need them to go across columns, leave the first parameter in SEQUENCE blank and use the number of days * 4 as the second parameter:

=FILTER(SEQUENCE(,A1*4,B1,0.25),(WEEKDAY(SEQUENCE(,A1*4,B1,0.25))>1)*(WEEKDAY(SEQUENCE(,A1*4,B1,0.25))<7))

One final bonus, if you had holidays you wanted to omit from the days in question, this formula would omit them:

=FILTER(SEQUENCE(A1*4,,B1,0.25),(WEEKDAY(SEQUENCE(A1*4,,B1,0.25))>1)*(WEEKDAY(SEQUENCE(A1*4,,B1,0.25)*(NETWORKDAYS(SEQUENCE(A1*4,,B1,0.25),SEQUENCE(A1*4,,B1,0.25),C1:C2)))<7))

In that formula I only used two holidays in cells C1:C2 (note they do not have to be locked as these are all Array operations).

There's probably a way to do this with older versions of Excel, but that eludes me at the moment!

Hope that helps. Someone!

 

[jdellasala]

Have to admit the formula posted by Pete_UK in your other post is much more graceful, on top of which I wasn't paying attention to the WEEKDAY parameters and really only needed one call to it with option 2 for the return values, so the formula with holidays excluded would be:

=FILTER(SEQUENCE(A1*4,,B1,0.25),(WEEKDAY(SEQUENCE(A1*4,,B1,0.25),2)<6)*(NETWORKDAYS(SEQUENCE(A1*4,,B1,0.25),SEQUENCE(A1*4,,B1,0.25),C1:C2)))

Maybe someone else can use it!