How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays

[Jeremy901]

Hi,
I am trying to create a tricky formula and have come close twice but not exactly what I need, maybe you could help?

I have a quantity of hours (project) that needs to have a specific start and end date. Then that quantity of hours between specific dates needs to be spread across specific months.

1st problem- The months start and end dates aren't exactly as per the generic calendar so must be specified in a separate cell.

2nd problem- a value in a different cell defines how many hours can be burnt from the project total per day.

3rd problem- The formula must realise that Mon- Fri are full working days but Sat and Sunday are 1/4 of the full working day capacity/burn rate.

4th Problem- The formula must also recognise holidays when all operations are shutdown (typically 10days over Christmas period depending on calendar days and how they fall)

Example:
A project has a total of 7,700 hours. To be worked at full capacity Monday-Friday, 1/4 capacity Saturday and Sunday and excluding holiday shutdown (23/DEC/23 - 01/JAN/24).

The project start is 14/DEC/24 and the end date is 19/JAN/24 (7700 hrs must be spread between these dates according to the capacity burn on ITEM 3=330 hrs a day but must also account for the calendar start/ends as well as including holiday shutdown days(all seen in this message))

The capacity hours that can be burnt in a day are set values depending on which item type is being worked on. IF item 3=330hrs per day, item A=325, item 5=420, item 6=450 , item 8= 275 & item PL=100.

Calendar start end dates are as follows:
DEC start= 28/NOV/23.
DEC end= 31/DEC/23 (no working days from/to 23/12/23 - 01/01/2024, last day of work is 22nd DEC).
JAN start= 01/JAN/24 (no working days on the 01/JAN/24) so actual start is the 2nd JAN.
JAN end= 31/JAN/24.

Maybe you could suggest a formula for these parameters??

I've tried a couple of ways that seem to be almost correct but they are not 100% and I need better accuracy. For example I had two projects of 7,700 on item 3 (daily burn 330hrs) that start in December ( one on the 14-DEC and the other on the 17-DEC) and they both end on JAN 19th. However the hours allocated in January aren't the same because the formula I use breaks the total hours between months as a percentage of 100 so while January should be the same (because it has the same possible amount of days to burn daily hours) it is different because my formula spreads more hours into December due to the earlier start date.

I tried to attach a picture of this explanation but I cant paste into this message box or attach elsewhere. I'd be happy to have a video call to show visually if that helps.

 

[Jeremy901]

The above is what I have been using. But as you can see networkintl formula accounts for 6 working days (function 11) when i need 5.5 a week (there's no function for decimals). & the thing that made me realise it wasn't correct was actually the two lower projects that have 7700 hours and end on the same day in January so therefore they should have the same hours allocated from the project in January as they run through the same days.

 

[felixstraube]

First some questions:

1. What do you mean by "start and end dates aren't exactly as per the generic calendar"? How do they look? You only show them for January.
2. Where is the value that defines how many hours can be burned per day (problem 2). I don't see that value in your image.
3. Where do you get the 7700 hours of your example from? Or is it just a value you enter in column "Hours"?
4. Is the end date a day you are working on the project? Because if it is for your example (second data row, of 7700 hours starting 14-dec ending 19-jan) you have 27 days of work, not 26 as you show in you image.
5. Where do you define what item type (IF item 3=330hrs per day, item A=325, item 5=420, item 6=450 , item 8= 275 & item PL=100) is worked on? I don't see that in your image.
6. Adding all the hour you can work at a rate of 330 hours per day on weekday and 1/4*330 hours per weekend day and excluding the holidays mentioned I sum a total of 7425 hours (and that is including the day of the end date 19-Jan, which I'm not sure i should). So basically you are never going to finish your project of 7700 hours in that time frame.

 

[felixstraube]

To calculate the hours burned by the project I did this:
I know that this isn't a solution to your problem, but are this number correct? If they are we can pack it all into a formula and filter it by month as you need, so you get the hours burned per month.

ProjectHours.xlsx
	K	L	M	N	O	P	Q
1						From example	330
2
3							7425
4				Dates	Is Working day?	Burn rate	Hour burned per day
5	Holidays			2023-12-14 Thu	TRUE	1	330
6	2023-12-23			2023-12-15 Fri	TRUE	1	330
7	2023-12-24			2023-12-16 Sat	TRUE	0.25	82.5
8	2023-12-25			2023-12-17 Sun	TRUE	0.25	82.5
9	2023-12-26			2023-12-18 Mon	TRUE	1	330
10	2023-12-27			2023-12-19 Tue	TRUE	1	330
11	2023-12-28			2023-12-20 Wed	TRUE	1	330
12	2023-12-29			2023-12-21 Thu	TRUE	1	330
13	2023-12-30			2023-12-22 Fri	TRUE	1	330
14	2023-12-31			2023-12-23 Sat	FALSE	0.25	0
15	2024-01-01			2023-12-24 Sun	FALSE	0.25	0
16				2023-12-25 Mon	FALSE	1	0
17				2023-12-26 Tue	FALSE	1	0
18				2023-12-27 Wed	FALSE	1	0
19				2023-12-28 Thu	FALSE	1	0
20				2023-12-29 Fri	FALSE	1	0
21				2023-12-30 Sat	FALSE	0.25	0
22				2023-12-31 Sun	FALSE	0.25	0
23				2024-01-01 Mon	FALSE	1	0
24				2024-01-02 Tue	TRUE	1	330
25				2024-01-03 Wed	TRUE	1	330
26				2024-01-04 Thu	TRUE	1	330
27				2024-01-05 Fri	TRUE	1	330
28				2024-01-06 Sat	TRUE	0.25	82.5
29				2024-01-07 Sun	TRUE	0.25	82.5
30				2024-01-08 Mon	TRUE	1	330
31				2024-01-09 Tue	TRUE	1	330
32				2024-01-10 Wed	TRUE	1	330
33				2024-01-11 Thu	TRUE	1	330
34				2024-01-12 Fri	TRUE	1	330
35				2024-01-13 Sat	TRUE	0.25	82.5
36				2024-01-14 Sun	TRUE	0.25	82.5
37				2024-01-15 Mon	TRUE	1	330
38				2024-01-16 Tue	TRUE	1	330
39				2024-01-17 Wed	TRUE	1	330
40				2024-01-18 Thu	TRUE	1	330
41				2024-01-19 Fri	TRUE	1	330
42				2024-01-20 Sat	TRUE		0
43				2024-01-21 Sun	TRUE		0
44				2024-01-22 Mon	TRUE		0
Sheet1

Cell Formulas
Range	Formula
Q3	Q3	=SUM(Q5:Q44)
N5	N5	=C6
O5:O44	O5	=WORKDAY.INTL(N5-1,1,"0000000",Holidays)=N5
P5:P41	P5	=0.25+0.75*(WEEKDAY(N5,2)<6)
N6:N44	N6	=N5+1
Q5:Q44	Q5	=$Q$1*P5*O5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N5:N44	Expression	=WEEKDAY(N5,2)>5	text	NO

 

[Jeremy901]

Firstly, thanks for your reply and time, I appreciate it. I have answered your questions as follows:

1.

[1]

2. I did not have that value built into my previous formulas.

The previous formula in my image was basically breaking the hours into a percentage and splitting that percentage across the months but as you see the two projects with 7700 hours were not being allocated the same amount in January because splitting a percentage doesn’t align with burnable days in the month.

[2]

To get the above figures in [2] (correct ones) I have made a calendar with each day’s burnable amount (as you have in your image) and am manually summing between start and end dates. When the project goes between more than one month, I am manually summing the correct project days from that calendar in that month. This takes time with many projects and is why I was trying to work out a formula so that I can just drag across the months in fig [1].

3. 7700 hours is the total hours of the project worked out in a different place and then its plugged into this spreadsheet to be seen spread through the calendar in fig [1] correctly.

NOTE: Sometimes the project hours may be more than the available hours in the calendar burn days as seen in fig [2] where the project needs 7700 but the calendar according to burn rates only has 7351 hours. This is where I can see where a project may exceed the predicted end date.

4. The start and end dates are working days. You’re correct that it should be 27 not 26. I have amended this as seen in fig [2]

5. Not part of my previous equation and I wasn’t sure how to do that in a formula. I assume IF functions, but I couldn’t get the hours spread right so I didn’t even go that far as to allocate the various item types.

6. See NOTE in answer 3. This is okay as an error either more or less due to the calendar burn rates. This is how I see when an project forecast is unlikely or more than likely to meet its estimated start/end dates. You can see in fig [2] that I have two columns of hours. The black font hours are forecasts (predictions) where as the green font hours next to that column is as you have worked out the available hours according to the burn rates on the calendar days.

NOTE: I have added 10% to the daily burns so the green font figures will be showing what you have done but 10% more. See fig [3] below:

 

[felixstraube]

Thanks for your reply.
How about this?:
"Holidays" is a table.

Here is the file to download: ProjectHours.zip

ProjectHours.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2						01-12-2023	01-01-2024	01-02-2024	29-02-2024	04-04-2024	02-05-2024	30-05-2024	04-07-2024	01-08-2024	29-08-2024	03-10-2024	31-10-2024	28-11-2024	01-01-2025	01-02-2025	01-03-2025
3						31-12-2023	31-01-2024	28-02-2024	03-04-2024	01-05-2024	29-05-2024	03-07-2024	31-07-2024	28-08-2024	02-10-2024	30-10-2024	27-11-2024	31-12-2024	31-01-2025	28-02-2025	03-04-2025
4	Working Days	Burn Rate	Hour needed to complete the proyect	Forecast calculated hours		Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Add new columns to the left of this		Holidays
5	36	330	12,878.0	9,405.0		3,960.0	5,445.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			23-12-2023
6	27	330	7,700.0	7,425.0		2,475.0	4,950.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			24-12-2023
7	24	330	7,700.0	6,682.5		1,732.5	4,950.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			25-12-2023
8																								26-12-2023
9																								27-12-2023
10																								28-12-2023
11																								29-12-2023
12																								30-12-2023
13																								31-12-2023
14																								01-01-2024
Sheet1

Cell Formulas
Range	Formula
I2:W2	I2	=H3+1
H5:W7	H5	=LET( projStart, $A5, projEnd, $B5, monthStart, H$2, monthEnd, H$3, projBurnRate, $D5, d, SEQUENCE(projEnd-projStart+1,, projStart), fd, FILTER(d, (d>=monthStart)*(d<=monthEnd)), isWorkDay, WORKDAY.INTL(fd-1,1,"0000000",Holidays)=fd, burnRate, (0.25+0.75*(WEEKDAY(fd,2)<6))*projBurnRate, IFERROR(SUM(isWorkDay*burnRate),0) )
C5:C7	C5	=NETWORKDAYS.INTL(A5,B5,"0000000",Holidays)
F5:F7	F5	=SUM(H5:X5)

 

[Jeremy901]

Well done you, looks perfect.

Quick question though and I assume its an easy one. I populated a different project that leaks into February to test if the numbers matched mine and February was incorrect. Is that because you've only made the burn rate calendar up to 22nd Jan 2024?
If so then If i complete the calendar for the entire 2024 and add holidays to the holidays table will all the months populate correctly as the three in your example have?

 

[felixstraube]

It should work fine with any dates you entre in Start and End columns (being end > start).
Can you show me what you see and what result you are expecting?

If I set the end date of the second project to 19/02/2024 i'll get this:

ProjectHours.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2								01/12/2023	01/01/2024	01/02/2024	29/02/2024	04/04/2024	02/05/2024	30/05/2024	04/07/2024	01/08/2024	29/08/2024	03/10/2024	31/10/2024	28/11/2024	01/01/2025	01/02/2025	01/03/2025
3								31/12/2023	31/01/2024	28/02/2024	03/04/2024	01/05/2024	29/05/2024	03/07/2024	31/07/2024	28/08/2024	02/10/2024	30/10/2024	27/11/2024	31/12/2024	31/01/2025	28/02/2025	03/04/2025
4	Start	End	Working Days	Burn Rate	Hour needed to complete the proyect	Forecast calculated hours		Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Add new columns to the left of this		Holidays
5	08/12/2023	22/01/2024	36	330	12,878.0	9,405.0		3,960.0	5,445.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			23/12/2023
6	14/12/2023	19/02/2024	58	330	7,700.0	15,180.0		2,475.0	7,920.0	4,785.0	-	-	-	-	-	-	-	-	-	-	-	-	-			24/12/2023
7	17/12/2023	19/01/2024	24	330	7,700.0	6,682.5		1,732.5	4,950.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			25/12/2023
8																										26/12/2023
9																										27/12/2023
10																										28/12/2023
11																										29/12/2023
12																										30/12/2023
13																										31/12/2023
14																										01/01/2024
Sheet1

Cell Formulas
Range	Formula
I2:W2	I2	=H3+1
H5:W7	H5	=LET( projStart, $A5, projEnd, $B5, monthStart, H$2, monthEnd, H$3, projBurnRate, $D5, d, SEQUENCE(projEnd-projStart+1,, projStart), fd, FILTER(d, (d>=monthStart)*(d<=monthEnd)), isWorkDay, WORKDAY.INTL(fd-1,1,"0000000",Holidays)=fd, burnRate, (0.25+0.75*(WEEKDAY(fd,2)<6))*projBurnRate, IFERROR(SUM(isWorkDay*burnRate),0) )
C5:C7	C5	=NETWORKDAYS.INTL(A5,B5,"0000000",Holidays)
F5:F7	F5	=SUM(H5:X5)

which seems correct adding manually the numbers in my control sheet:

ProjectHours.xlsx
	K	L	M	N	O	P	Q
1						From example	330
2
3							15180
4				Dates	Is Working day?	Burn rate	Hour burned per day
5	Holidays			2023/12/14 Thu	TRUE	1	330
6	23/12/2023			2023/12/15 Fri	TRUE	1	330
7	24/12/2023			2023/12/16 Sat	TRUE	0.25	82.5
8	25/12/2023			2023/12/17 Sun	TRUE	0.25	82.5
9	26/12/2023			2023/12/18 Mon	TRUE	1	330
10	27/12/2023			2023/12/19 Tue	TRUE	1	330
11	28/12/2023			2023/12/20 Wed	TRUE	1	330
12	29/12/2023			2023/12/21 Thu	TRUE	1	330
13	30/12/2023			2023/12/22 Fri	TRUE	1	330
14	31/12/2023			2023/12/23 Sat	FALSE	0.25	0
15	01/01/2024			2023/12/24 Sun	FALSE	0.25	0
16				2023/12/25 Mon	FALSE	1	0
17				2023/12/26 Tue	FALSE	1	0
18				2023/12/27 Wed	FALSE	1	0
19				2023/12/28 Thu	FALSE	1	0
20				2023/12/29 Fri	FALSE	1	0
21				2023/12/30 Sat	FALSE	0.25	0
22				2023/12/31 Sun	FALSE	0.25	0
23				2024/01/01 Mon	FALSE	1	0
24				2024/01/02 Tue	TRUE	1	330
25				2024/01/03 Wed	TRUE	1	330
26				2024/01/04 Thu	TRUE	1	330
27				2024/01/05 Fri	TRUE	1	330
28				2024/01/06 Sat	TRUE	0.25	82.5
29				2024/01/07 Sun	TRUE	0.25	82.5
30				2024/01/08 Mon	TRUE	1	330
31				2024/01/09 Tue	TRUE	1	330
32				2024/01/10 Wed	TRUE	1	330
33				2024/01/11 Thu	TRUE	1	330
34				2024/01/12 Fri	TRUE	1	330
35				2024/01/13 Sat	TRUE	0.25	82.5
36				2024/01/14 Sun	TRUE	0.25	82.5
37				2024/01/15 Mon	TRUE	1	330
38				2024/01/16 Tue	TRUE	1	330
39				2024/01/17 Wed	TRUE	1	330
40				2024/01/18 Thu	TRUE	1	330
41				2024/01/19 Fri	TRUE	1	330
42				2024/01/20 Sat	TRUE	0.25	82.5
43				2024/01/21 Sun	TRUE	0.25	82.5
44				2024/01/22 Mon	TRUE	1	330
45				2024/01/23 Tue	TRUE	1	330
46				2024/01/24 Wed	TRUE	1	330
47				2024/01/25 Thu	TRUE	1	330
48				2024/01/26 Fri	TRUE	1	330
49				2024/01/27 Sat	TRUE	0.25	82.5
50				2024/01/28 Sun	TRUE	0.25	82.5
51				2024/01/29 Mon	TRUE	1	330
52				2024/01/30 Tue	TRUE	1	330
53				2024/01/31 Wed	TRUE	1	330
54				2024/02/01 Thu	TRUE	1	330
55				2024/02/02 Fri	TRUE	1	330
56				2024/02/03 Sat	TRUE	0.25	82.5
57				2024/02/04 Sun	TRUE	0.25	82.5
58				2024/02/05 Mon	TRUE	1	330
59				2024/02/06 Tue	TRUE	1	330
60				2024/02/07 Wed	TRUE	1	330
61				2024/02/08 Thu	TRUE	1	330
62				2024/02/09 Fri	TRUE	1	330
63				2024/02/10 Sat	TRUE	0.25	82.5
64				2024/02/11 Sun	TRUE	0.25	82.5
65				2024/02/12 Mon	TRUE	1	330
66				2024/02/13 Tue	TRUE	1	330
67				2024/02/14 Wed	TRUE	1	330
68				2024/02/15 Thu	TRUE	1	330
69				2024/02/16 Fri	TRUE	1	330
70				2024/02/17 Sat	TRUE	0.25	82.5
71				2024/02/18 Sun	TRUE	0.25	82.5
72				2024/02/19 Mon	TRUE	1	330
73				2024/02/20 Tue	TRUE		0
74				2024/02/21 Wed	TRUE		0
Sheet1 (3)

Cell Formulas
Range	Formula
Q3	Q3	=SUM(Q5:Q74)
N5	N5	=C6
O5:O74	O5	=WORKDAY.INTL(N5-1,1,"0000000",Holidays34)=N5
P5:P72	P5	=0.25+0.75*(WEEKDAY(N5,2)<6)
N6:N74	N6	=N5+1
Q5:Q74	Q5	=$Q$1*P5*O5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N5:N74	Expression	=WEEKDAY(N5,2)>5	text	NO

Both give 15180 hours.

 

[Jeremy901]

Actually, I take it back. I've seen why February was more than I projected. Because I have bank holidays from February to November as 1/4 capacity too. So the dates in the below picture are also 1/4 capacity (excluding new years day which as mentioned before is 0 capacity:

So I think you have it solved completely according to my previous posts (I can tweek it to get the bank holidays as 1/4 capacity myself).

Any chance of setting up a video call one day and walking me through the coding of the formula to make sure I understand it correctly. Like is this a custom formula? I've never seen LET function used or your code function fd-1,1,"0000000",holidays) etc.

 

[Jeremy901]

For visual this is what I was talking about: