Adding Hours & Minutes to Dates & Times w/in Working Hours

[reberryjr]

I recently posted a question on how to identify the time taken to process a task. The answer to the question was to use this formula:

=(NETWORKDAYS(BD2,BF2,Variables!$A$2:$A$34)-1)*(Variables!$E$2-Variables!$C$2)+IF(NETWORKDAYS(BF2,BF2,Variables!$A$2:$A$34),MEDIAN(MOD(BF2,1),Variables!$E$2,Variables!$C$2),Variables!$E$2)-MEDIAN(NETWORKDAYS(BD2,BD2,Variables!$A$2:$A$34)*MOD(BD2,1),Variables!$E$2,Variables!$C$2)

I'd like to be able to add X minutes to the Task Start Date & Time so that I can identify the Estimated Completed Date & Time. I would need the result to return a date & time that falls within Network Days AND working hours. In the underlying example, the SLA for the task is 30 minutes. Since working hours are 08:00 - 16:00 and 7/4/24 was a holiday, the due date would be pushed to 7/5/24 & 8:15.

Worksheet structure:

 

[reberryjr]

When I modify the formula from post 6 to match your layout, I get this:

2024-08-13_2.xlsx
	BD										BN	BO	BP
1	Task StartDate										SLA Minutes	Result	Expected
2	03-Jul-24 15:45										0:30	05-Jul-24 08:15	05-Jul-24 08:15
3	30-Jul-24 14:08										4:00	31-Jul-24 10:08	?
Data

Cell Formulas
Range	Formula
BO2:BO3	BO2	=WORKDAY(BD2,CEILING((BN2+MOD(BD2,1)-Variables!$C$2)/(Variables!$D$2-Variables!$C$2),1)-1,Holidays)+MOD(BD2,1)+BN2-CEILING(MOD(BD2,1)+BN2-Variables!$C$2,Variables!$D$2-Variables!$C$2)+Variables!$D$2-Variables!$C$2

2024-08-13_2.xlsx
	A	B	C	D
1	Holidays		Start Time	End Time
2	4-Jul-24		08:00	16:00
Variables

(note: I have July 4 as the only date in the holiday list as I don't know what you need)

I said I was tired right? My formula was correct. One of the reference cells wasn't. Thank you for your help!!!!

 

[Asbestos_Jen]

Glad to help. Thanks for the feedback.