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.

 

[felixstraube]

Here you go including the Bank Holidays:
(In the next post i'll explain how the formula works)

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	AA	AB
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		1/4 Burn Rate Holidays
5	08/12/2023	22/01/2024	36	363	12,878.0	10,345.5		4,356.0	5,989.5	-	-	-	-	-	-	-	-	-	-	-	-	-	-			23/12/2023		05/02/2024
6	14/12/2023	19/01/2024	27	363	7,700.0	8,167.5		2,722.5	5,445.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			24/12/2023		18/03/2024
7	17/12/2023	19/01/2024	24	363	7,700.0	7,350.8		1,905.8	5,445.0	-	-	-	-	-	-	-	-	-	-	-	-	-	-			25/12/2023		01/04/2024
8	20/01/2024	11/02/2024	23	363	7,700.0	5,898.8		-	3,267.0	2,631.8	-	-	-	-	-	-	-	-	-	-	-	-	-			26/12/2023		06/05/2024
9																										27/12/2023		03/07/2024
10																										28/12/2023		05/08/2024
11																										29/12/2023		28/10/2024
12																										30/12/2023
13																										31/12/2023
14																										01/01/2024
Sheet1

Cell Formulas
Range	Formula
I2:W2	I2	=H3+1
H5:W8	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, isFullWorkingDay, WORKDAY.INTL(fd-1,1,"0000000",HolidaysQuarterBurnRate)=fd, burnRate, (0.25+0.75*(WEEKDAY(fd,2)<6)*isFullWorkingDay)*projBurnRate, IFERROR(SUM(isWorkDay*burnRate),0) )
C5:C8	C5	=NETWORKDAYS.INTL(A5,B5,"0000000",Holidays)
F5:F8	F5	=SUM(H5:X5)

 

[felixstraube]

LET is a function that lets you define variables and reuse them. It is not always necessary to use LET, in fact the whole formula could be written without LET function but I like it because it make it more readable and easier to work on.
How does LET work? You can read this here: https://support.microsoft.com/en-au/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999#:~:text=The%20LET%20function%20assigns%20names,through%20Excel's%20native%20formula%20syntax.
Basically you define variables in pairs of arguments, and the last argument is the final calculation.
The example

=LET(x, 1, y, 2, x*y)

you define x assign it the value 1, define y assign the value 2 and last the calculation x*y.
Now the value you assign to a variable (or "name" as Microsoft calls them in their support page) can be also a formula. For example:

=LET(x, A1+3, y, B1*2, x*y)

WORKDAY.INTL like WORKDAY gives you the next workday starting at a given date and going forwards or backwards a given number of days, with the difference that you can define custom weekend.
WORKDAY.INTL function - Microsoft Support
For the 3rd parameter you can input a number (1-7 and 11-17) or you can write a string 7 characters long (which i learn pretty recently from this forum). So that you can customize your weekend. A 0 is a working day, a 1 is a weekend starting on monday (refer to support page).
Now if you include the las parameter (Holidays), and enter here a range of dates, they are excluded from working days.
So by doing this:

=WORKDAY.INTL(A1-1,1,"0000000",Holidays)

Given the date in A1, subtracting 1 day, going forwards 1 day, with no weekend, and excluding holidays, i will get the same date again from A1 if A1 is not a holiday and get next workday different from A1 if A1 is one of the holidays.
So by doing this comparison
=WORKDAY.INTL(A1-1,1,"0000000",Holidays)=A1
I will know if A1 is a holiday or not.

Now to our formula:
Lets look at a small example:

ProjectHours2.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1									Proj Burn rate	363
2
3							depends on Holidays	depends on Bank Holidays		3539.25
4	Start	End	Working Days			Dates	Is Working day	Is Full Working Day	Burn rate	Hour burned per day		Holidays		1/4 Burn Rate Holidays
5	20/12/2023	10/01/2024	12			2023/12/20 Wed	TRUE	TRUE	1	363		23/12/2023		05/02/2024
6						2023/12/21 Thu	TRUE	TRUE	1	363		24/12/2023		18/03/2024
7						2023/12/22 Fri	TRUE	TRUE	1	363		25/12/2023		01/04/2024
8						2023/12/23 Sat	FALSE	TRUE	0.25	0		26/12/2023		06/05/2024
9						2023/12/24 Sun	FALSE	TRUE	0.25	0		27/12/2023		03/07/2024
10						2023/12/25 Mon	FALSE	TRUE	1	0		28/12/2023		05/08/2024
11						2023/12/26 Tue	FALSE	TRUE	1	0		29/12/2023		28/10/2024
12						2023/12/27 Wed	FALSE	TRUE	1	0		30/12/2023		03/01/2024
13						2023/12/28 Thu	FALSE	TRUE	1	0		31/12/2023
14						2023/12/29 Fri	FALSE	TRUE	1	0		01/01/2024
15						2023/12/30 Sat	FALSE	TRUE	0.25	0
16						2023/12/31 Sun	FALSE	TRUE	0.25	0
17						2024/01/01 Mon	FALSE	TRUE	1	0
18						2024/01/02 Tue	TRUE	TRUE	1	363
19						2024/01/03 Wed	TRUE	FALSE	0.25	90.75
20						2024/01/04 Thu	TRUE	TRUE	1	363
21						2024/01/05 Fri	TRUE	TRUE	1	363
22						2024/01/06 Sat	TRUE	TRUE	0.25	90.75
23						2024/01/07 Sun	TRUE	TRUE	0.25	90.75
24						2024/01/08 Mon	TRUE	TRUE	1	363
25						2024/01/09 Tue	TRUE	TRUE	1	363
26						2024/01/10 Wed	TRUE	TRUE	1	363
27						2024/01/11 Thu
28						2024/01/12 Fri
29						2024/01/13 Sat
Sheet1 (2)

Cell Formulas
Range	Formula
K3	K3	=SUM(K5:K44)
D5	D5	=NETWORKDAYS.INTL(B5,C5,"0000000",Holidays)
H5:H26	H5	=WORKDAY.INTL(G5-1,1,"0000000",Holidays)=G5
I5:I26	I5	=WORKDAY.INTL(G5-1,1,"0000000",HolidaysQuarterBurnRate)=G5
J5:J26	J5	=0.25+0.75*(WEEKDAY(G5,2)<6)*I5
K5:K26	K5	=$K$1*J5*H5
G5	G5	=B5
G6:G29	G6	=G5+1

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

First we create the list of dates from start to end.
Then we calculate if it is a working day or not (column H) considering Holidays (from 23dec to 1jan).
Then we calculate if it is a full working day (column I) depending on the Bank Holidays (I added 03/01/2024 just to see how it affects the result).
Then we calculate the burn rate. Here WEEKDAY(G5,2) gives me 1 for monday to 5 friday, 6 and 7 are sat and sun. So if the number is smaller than 6 its a normal weekday. So if it is a normal weekday and it is a full working day (not a bank holiday) we end up adding 0.25 to 0.75 and get 1. Else if it is a weekend or it is a bank Holiday (WEEKDAY(G5,2)<6) will return FALSE or Full working day will be FALSE. And, if you use a logical data type in a calculation TRUE is considered 1, and FALSE a 0. So if any of these two are 0 you get only 0.25 as a result.
Then we multiply the logical column "Is Working Day" with the "Burn Rate" times the Proj Burn Rate (in our example 363) and get the hours burn for that each day.
Last we sum up all the calculated hour.

Now we use the LET function to do the same.
First we create the sequence of dates with this part of the function:

d, SEQUENCE(projEnd-projStart+1,, projStart),

This will give you an vertical array of dates from start to end date.
Then in the next line you filter this array with the start and end days of month of the current column:

fd, FILTER(d, (d>=monthStart)*(d<=monthEnd)),

After we check if it is a working day:

isWorkDay, WORKDAY.INTL(fd-1,1,"0000000",Holidays)=fd,

See if it is a fullWorking day:

isFullWorkingDay, WORKDAY.INTL(fd-1,1,"0000000",HolidaysQuarterBurnRate)=fd,

and calculate the burn rate for each day:

burnRate, (0.25+0.75*(WEEKDAY(fd,2)<6)*isFullWorkingDay)*projBurnRate,

All of the above produce vertical arrays of the same lenght as days are left after filtering by month start and end.
Last we multply isWorkDay times burnRate and sum all up.
For the case in which filter function returns 0 items, actually #Calc error, (no proyect days in the given month) we use the IFERROR to return 0.

Hope all this makes sense.