Add a specified number of working hours to a date & time, given working day start, end and break times and holidays

[AquaBuoy]

Hi, I’m looking for a formula that provides a more granular version of Excel’s WORKDAY function. It needs to add a decimal number of working hours to a date and time value, accounting for an 08:00 to 17:00 workday (Monday to Friday), with a lunch break from 13:00 to 14:00 each day (those times need to be configurable), and a list of holidays to produce a due date and time.

So, given these working hours (Monday to Friday) and list of Holidays (J2:J3):

WorkHours_Calculations.xlsx
	G	H	I	J
1	Working Times (Mon to Fri)			Holidays
2	WorkDay_Start	08:00		06 March 2024
3	LunchBreak_Start	13:00		01 May 2024
4	LunchBreak_End	14:00
5	WorkDay_End	17:00
6	Work_Hrs_per_Day	8
Sheet1

Cell Formulas
Range	Formula
H6	H6	=((LunchBreak_Start-WorkDay_Start)+(WorkDay_End-LunchBreak_End))*24

Named Ranges
Name	Refers To	Cells
LunchBreak_End	=Sheet1!$H$4	H6
LunchBreak_Start	=Sheet1!$H$3	H6
WorkDay_End	=Sheet1!$H$5	H6
WorkDay_Start	=Sheet1!$H$2	H6

The formula needs to replicate the manually calculated values in the Due Date & Time column (C).

WorkHours_Calculations.xlsx
	A	B	C
1	Start Date & Time	# Hours	Due Date & Time
2	Mon, 04 Mar 2024 08:00	1.25	Mon, 04 Mar 2024 09:15
3	Mon, 04 Mar 2024 11:00	3.00	Mon, 04 Mar 2024 15:00
4	Mon, 04 Mar 2024 15:03	4.00	Tue, 05 Mar 2024 10:03
5	Mon, 04 Mar 2024 10:00	12.00	Tue, 05 Mar 2024 15:00
6	Mon, 04 Mar 2024 16:00	8.00	Tue, 05 Mar 2024 16:00
7	Tue, 05 Mar 2024 07:30	2.50	Tue, 05 Mar 2024 10:30
8	Tue, 05 Mar 2024 13:30	2.50	Tue, 05 Mar 2024 16:30
9	Tue, 05 Mar 2024 15:00	4.00	Thu, 07 Mar 2024 10:00
10	Tue, 05 Mar 2024 18:00	8.00	Thu, 07 Mar 2024 17:00
11	Fri, 08 Mar 2024 12:00	32.00	Thu, 14 Mar 2024 12:00
Sheet1

Any help is greatly appreciated.

 

[Tetra201]

The following (monstrous) formula returns the expected results for the posted dataset:

=LET(aaa,MOD(A2,1),bbb,MOD(B2,$H$6)/24,ddd,WORKDAY(A2,INT(B2/$H$6),$J$2:$J$11)+bbb+MAX(IF(aaa<=LunchBreak_Start,WorkDay_Start,LunchBreak_End),MIN(aaa,WorkDay_End))+(MOD(LunchBreak_Start-aaa,1)<bbb)*(LunchBreak_End-LunchBreak_Start)+(MOD(WorkDay_End-aaa,1)<bbb)*MOD(WorkDay_Start-WorkDay_End,1),WORKDAY(ddd-1,1,$J$2:$J$11)+MOD(ddd,1))

 

[AquaBuoy]

The following (monstrous) formula returns the expected results for the posted dataset:

=LET(aaa,MOD(A2,1),bbb,MOD(B2,$H$6)/24,ddd,WORKDAY(A2,INT(B2/$H$6),$J$2:$J$11)+bbb+MAX(IF(aaa<=LunchBreak_Start,WorkDay_Start,LunchBreak_End),MIN(aaa,WorkDay_End))+(MOD(LunchBreak_Start-aaa,1)<bbb)*(LunchBreak_End-LunchBreak_Start)+(MOD(WorkDay_End-aaa,1)<bbb)*MOD(WorkDay_Start-WorkDay_End,1),WORKDAY(ddd-1,1,$J$2:$J$11)+MOD(ddd,1))

Thanks ever so much Tetra201. This is a huge help. I noticed two problems:
1. My expected result for row 11 (32 hours added to 8 Mar 2024 12:00) above was incorrect. I hadn't accounted for the holiday. My apologies.
2. The formula doesn't appear to be accurate when the start date & time is outside of business hours, on a holiday or on a weekend.
Could I impose on you to have another go at it to get all the Calculated dates & times in column D to match the Expected values in column C?

WorkHours_Calculations.xlsx
	A	B	C	D	E	F	G	H	I
1	Start Date & Time	# Hours	Expected Due Date & Time	Calculated Due Date & Time		Working Hours (Monday to Friday)			Holidays_List
2	Mon, 04 Mar 2024 08:00	1.25	Mon, 04 Mar 2024 09:15	Mon, 04 Mar 2024 09:15		WorkDay_Start	8:00:00 AM		Wed, 06 Mar 2024
3	Mon, 04 Mar 2024 11:00	3.00	Mon, 04 Mar 2024 15:00	Mon, 04 Mar 2024 15:00		LunchBreak_Start	1:00:00 PM		Wed, 01 May 2024
4	Mon, 04 Mar 2024 15:03	4.00	Tue, 05 Mar 2024 10:03	Tue, 05 Mar 2024 10:03		LunchBreak_End	2:00:00 PM
5	Mon, 04 Mar 2024 10:00	12.00	Tue, 05 Mar 2024 15:00	Tue, 05 Mar 2024 15:00		WorkDay_End	5:00:00 PM
6	Mon, 04 Mar 2024 16:00	8.00	Tue, 05 Mar 2024 16:00	Tue, 05 Mar 2024 16:00		Work_Hrs_per_Day	8
7	Tue, 05 Mar 2024 07:30	2.50	Tue, 05 Mar 2024 10:30	Tue, 05 Mar 2024 10:30
8	Tue, 05 Mar 2024 13:30	2.50	Tue, 05 Mar 2024 16:30	Tue, 05 Mar 2024 16:30
9	Tue, 05 Mar 2024 15:00	4.00	Thu, 07 Mar 2024 10:00	Thu, 07 Mar 2024 10:00
10	Tue, 05 Mar 2024 18:00	8.00	Thu, 07 Mar 2024 17:00	Thu, 07 Mar 2024 17:00
11	Fri, 08 Mar 2024 12:00	32.00	Thu, 14 Mar 2024 12:00	Fri, 15 Mar 2024 12:00
12	Mon, 04 Mar 2024 18:00	2.00	Tue, 05 Mar 2024 10:00	Mon, 04 Mar 2024 19:00
13	Wed, 06 Mar 2024 08:30	4.00	Thu, 07 Mar 2024 12:00	Thu, 07 Mar 2024 12:30
14	Wed, 06 Mar 2024 11:30	4.25	Thu, 07 Mar 2024 12:15	Thu, 07 Mar 2024 16:45
15	Wed, 06 Mar 2024 12:30	5.00	Thu, 07 Mar 2024 13:00	Thu, 07 Mar 2024 09:30
16	Wed, 06 Mar 2024 13:30	5.50	Thu, 07 Mar 2024 14:30	Thu, 07 Mar 2024 10:30
17	Wed, 06 Mar 2024 14:30	7.50	Thu, 07 Mar 2024 16:30	Thu, 07 Mar 2024 13:00
18	Wed, 06 Mar 2024 15:30	9.00	Fri, 08 Mar 2024 09:00	Thu, 07 Mar 2024 16:30
19	Sat, 09 Mar 2024 11:00	2.00	Mon, 11 Mar 2024 10:00	Mon, 11 Mar 2024 13:00
20	Sun, 10 Mar 2024 18:00	3.00	Mon, 11 Mar 2024 11:00	Mon, 11 Mar 2024 20:00
Sheet1

Cell Formulas
Range	Formula
G6	G6	=((LunchBreak_Start-WorkDay_Start)+(WorkDay_End-LunchBreak_End))*24
D2:D20	D2	=LET(aaa,MOD(A2,1),bbb,MOD(B2,Work_Hrs_per_Day)/24,ddd,WORKDAY(A2,INT(B2/Work_Hrs_per_Day),Holidays_List)+bbb+MAX(IF(aaa<=LunchBreak_Start,WorkDay_Start,LunchBreak_End),MIN(aaa,WorkDay_End))+(MOD(LunchBreak_Start-aaa,1)<bbb)*(LunchBreak_End-LunchBreak_Start)+(MOD(WorkDay_End-aaa,1)<bbb)*MOD(WorkDay_Start-WorkDay_End,1),WORKDAY(ddd-1,1,Holidays_List)+MOD(ddd,1))

Named Ranges
Name	Refers To	Cells
Holidays_List	=Sheet1!$I$2:$I$3	D2:D20
LunchBreak_End	=Sheet1!$G$4	G6, D2:D20
LunchBreak_Start	=Sheet1!$G$3	G6, D2:D20
Work_Hrs_per_Day	=Sheet1!$G$6	D2:D20
WorkDay_End	=Sheet1!$G$5	G6, D2:D20
WorkDay_Start	=Sheet1!$G$2	G6, D2:D20