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:

 

[Asbestos_Jen]

When I was searching for my issue, I found the thread below that will probably get you there: WORKDAY with Formulas, TIME Format & Work Hours

 

[felixstraube]

are you working from mon to fri?

 

[reberryjr]

are you working from mon to fri?

We have various holidays throughout the year, but the typical work week is M - F

 

[reberryjr]

When I was searching for my issue, I found the thread below that will probably get you there: WORKDAY with Formulas, TIME Format & Work Hours

It looks like this post is only showing how to calculate the working hours if there's an end date already. I have that part. I'm trying to ascertain how to add a specific amount of time to the start date, while making sure the result takes holidays and working hours into account. If I'm missing that part in the post you provided, I apologize.

 

[Asbestos_Jen]

At the link I provided, the start and finish times represent business hours. A1 contains the task start date, C1 is the time to complete the task, and B1 contains the formula to calculate end time. A post a few below the one I quoted corrects the statement "Then you can use this formula in C1" to use the formula in B1.

Hello kpeacock, welcome to MrExcel

Assuming you have daily start and finish times in E2 and F2, i.e. for your example

E2 = 08:00
F2 = 17:00

Then you can use this formula in C1

=WORKDAY(A1,CEILING((C1+MOD(A1,1)-E$2)/(F$2-E$2),1)-1,holidays)+MOD(A1,1)+C1-CEILING(MOD(A1,1)+C1-E$2,F$2-E$2)+F$2-E$2

Assumption is that A1 is a date and time within working hours, then formula should work for any (positive) time in C1.

 

[felixstraube]

Would this work for you?:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Holidays		Start time		End time		Task start	SLA (in minutes)	Result
2	04/07/2024		08:00		16:00		2024-07-03 15:45	30	2024-07-05 08:15
3							2024-07-03 15:45	480	2024-07-05 15:45
4							2024-07-03 15:45	495	2024-07-05 16:00
5							2024-07-03 15:45	510	2024-07-08 08:15
Sheet10

Cell Formulas
Range	Formula
I2:I5	I2	=LET(ts, G2, sla, H2, s, $C$2, e, $E$2, n, sla+10*24*60, m, SEQUENCE(n,,ts, TIME(0,1,0)), d, INT(m), t, VALUE(TEXT(MOD(m, 1), "hh:mm")), workingDay, (WORKDAY(d-1,1, Holidays)=d)*1, workingHour, (t>=s)*(t<e), sc, SCAN(0, workingDay*workingHour, LAMBDA(a,b, a+b)), INDEX(m, MATCH(sla, sc, 0)+1) )

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet10!$A$2:$A$34	I2:I5

 

[reberryjr]

At the link I provided, the start and finish times represent business hours. A1 contains the task start date, C1 is the time to complete the task, and B1 contains the formula to calculate end time. A post a few below the one I quoted corrects the statement "Then you can use this formula in C1" to use the formula in B1.

At the link I provided, the start and finish times represent business hours. A1 contains the task start date, C1 is the time to complete the task, and B1 contains the formula to calculate end time. A post a few below the one I quoted corrects the statement "Then you can use this formula in C1" to use the formula in B1.

My tired eyes saw that, but it didn't register with my brain. I apologize again. I did try to update the formula to fit my layout, but adding 4 hours to 7/30/2024 14:08 is returning 5/26/2027 14:08, so something's pretty off.

 

[reberryjr]

Would this work for you?:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Holidays		Start time		End time		Task start	SLA (in minutes)	Result
2	04/07/2024		08:00		16:00		2024-07-03 15:45	30	2024-07-05 08:15
3							2024-07-03 15:45	480	2024-07-05 15:45
4							2024-07-03 15:45	495	2024-07-05 16:00
5							2024-07-03 15:45	510	2024-07-08 08:15
Sheet10

Cell Formulas
Range	Formula
I2:I5	I2	=LET(ts, G2, sla, H2, s, $C$2, e, $E$2, n, sla+10*24*60, m, SEQUENCE(n,,ts, TIME(0,1,0)), d, INT(m), t, VALUE(TEXT(MOD(m, 1), "hh:mm")), workingDay, (WORKDAY(d-1,1, Holidays)=d)*1, workingHour, (t>=s)*(t<e), sc, SCAN(0, workingDay*workingHour, LAMBDA(a,b, a+b)), INDEX(m, MATCH(sla, sc, 0)+1) )

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet10!$A$2:$A$34	I2:I5

It looks like there are several functions I'm not familiar with. I will check this out when I'm a bit less sleepy.

 

[Asbestos_Jen]

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)