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

#### HighlandRanger

##### New Member
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?

### 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
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
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!

Replies
3
Views
91
Replies
1
Views
58
Replies
3
Views
182
Replies
1
Views
193
Replies
1
Views
39

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.

### Which adblocker are you using?

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

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