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

HighlandRanger

New Member
Joined
Feb 8, 2021
Messages
21
Office Version
  1. 365
Platform
  1. MacOS
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.
1614041292275.png


Any Help?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jdellasala

New Member
Joined
Dec 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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):

Excel Formula:
=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:

Excel Formula:
=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:

Excel Formula:
=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:

Excel Formula:
=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

New Member
Joined
Dec 11, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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:

Excel Formula:
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top