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

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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:

Excel 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.
1723566962780.png


Worksheet structure:
1723567185459.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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.
 
Upvote 0
Would this work for you?:

Book1.xlsx
ABCDEFGHI
1HolidaysStart timeEnd timeTask startSLA (in minutes)Result
204/07/202408:0016:002024-07-03 15:45302024-07-05 08:15
32024-07-03 15:454802024-07-05 15:45
42024-07-03 15:454952024-07-05 16:00
52024-07-03 15:455102024-07-08 08:15
Sheet10
Cell Formulas
RangeFormula
I2:I5I2=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
NameRefers ToCells
Holidays=Sheet10!$A$2:$A$34I2:I5
 
Last edited:
Upvote 0
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.
 
Upvote 0
Would this work for you?:

Book1.xlsx
ABCDEFGHI
1HolidaysStart timeEnd timeTask startSLA (in minutes)Result
204/07/202408:0016:002024-07-03 15:45302024-07-05 08:15
32024-07-03 15:454802024-07-05 15:45
42024-07-03 15:454952024-07-05 16:00
52024-07-03 15:455102024-07-08 08:15
Sheet10
Cell Formulas
RangeFormula
I2:I5I2=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
NameRefers ToCells
Holidays=Sheet10!$A$2:$A$34I2: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.
 
Last edited:
Upvote 0
When I modify the formula from post 6 to match your layout, I get this:

2024-08-13_2.xlsx
BDBNBOBP
1Task StartDateSLA MinutesResultExpected
203-Jul-24 15:450:3005-Jul-24 08:1505-Jul-24 08:15
330-Jul-24 14:084:0031-Jul-24 10:08?
Data
Cell Formulas
RangeFormula
BO2:BO3BO2=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
ABCD
1HolidaysStart TimeEnd Time
24-Jul-2408:0016:00
Variables


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

Forum statistics

Threads
1,223,375
Messages
6,171,735
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top