How to generate list of start dates

ac7

New Member
Joined
Jul 26, 2023
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello -

I am struggling with a formula and am hoping someone can provide some suggestions on how to accomplish what I need. In the month of March, I have 19 available working days (M-F only). With 8 hours a day, that gives me 152 hours available in the month. My shift starts at 6:00 and ends at 14:00. I have 13 jobs that need to be started in the month of March, beginning March 4th. I can only start these on a Monday through Friday. The approximate time between each job is 11.69 hours.

Working Days
19​
Working Hours
152​
Start of Day
6:00​
End of Day
14:00​
Starts
13​
Hours between starts
11.69​
Starting Date
3/4/2024​

I need to produce a list of dates to show what days I will be starting the jobs. Does anyone have suggestions on how I can best do this?

Thank you in advance!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
This does the trick I think, but it's clumsy. The workday function doesn't take arrays for inputs.

MrExcelPlayground21.xlsx
BCDEFGH
1Holidays
2Monday, March 4, 2024Tuesday, March 19, 2024
3Sunday, March 31, 202419Monday, May 27, 2024
4Starts13
5Start6:00
6End14:00
7Hours152
8Hours between starts11.69230769
9
100Monday, March 4, 2024
111.461538462Tuesday, March 5, 2024
122.923076923Wednesday, March 6, 2024
134.384615385Friday, March 8, 2024
145.846153846Monday, March 11, 2024
157.307692308Wednesday, March 13, 2024
168.769230769Thursday, March 14, 2024
1710.23076923Monday, March 18, 2024
1811.69230769Wednesday, March 20, 2024
1913.15384615Friday, March 22, 2024
2014.61538462Monday, March 25, 2024
2116.07692308Wednesday, March 27, 2024
2217.53846154Thursday, March 28, 2024
Sheet2
Cell Formulas
RangeFormula
B3B3=EOMONTH(B2,0)
C3C3=NETWORKDAYS.INTL(B2,B3,1,H2:H13)
C7C7=C3*(C6-C5)*24
C8C8=C7/C4
C10:C22C10=SEQUENCE(C4,1,0,(C8/24)*(1/(C6-C5)))
D10:D22D10=WORKDAY.INTL($B$2,C10,1,$H$2:$H$3)
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

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
Back
Top