Adding a number of working hours to networkdays

Juliaafreeman

New Member
Joined
Mar 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
This is the first time I'm posting on here, so hopefully I'm doing this right.

Here is what I am trying to achieve: I have cargo that is being delivered to a site. Once the cargo has been delivered we have 14 hours to unload the cargo with working hours being Monday - Friday 7:00 am - 5:30 pm (and holidays excluded). After the 14 hours we start being charged per hour for the additional time it takes to complete unloading the cargo. I am trying to figure out what the date and time from the arrival date is 14 working hours?

Here is an example below. The answer SHOULD be 10/29/2019 10:30 am. I have been googling this for DAYS and all I get is "how to use network days between two dates" but that's not what I need.

If anyone can help with this, it would be greatly appreciated.

1616766736699.png
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Johndinho

Board Regular
Joined
Mar 21, 2013
Messages
90
The formula in the yellow box in B would be
=A2+.583333333333333

You can work out any number of hours as a number by
Enter 14:00 into a cell
Format the cell to time
Format the cell to number

Then add the number (0.583333333333333 in this case) to the start time
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Can I assume that cargo can Only be delivered to site during your working hours?
So, earliest delivery is 7 AM, and latest is 5:30 PM, M-F?

Nevermind, just saw your Sample, the delivery is at 1:30 AM, which is Outside your working hours.
So, delivery can be 24 hours?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your arrival date is a Sunday so there are no working hours on that date.
Mondays would have 10:30 hours leaving 3:30 hours for Tuesday.
I calculated the hours to unload and the excess hours.
If the arrival is on a weekday, you will have to calculate the work hours completed on both the start and end dates.

Networkdays.xlsm
ABCDEF
1ArrivalCompletedCompletedExcessLoading time
2Sun Oct 27, 2019 1:30 AMTue Oct 29, 2019 10:30 AM14:00Start07:00 AM
3End05:30 PM
4Sun Oct 27, 2019 1:30 AMWed Oct 30, 2019 3:00 PM29:0015:00Hours10:30
5Sun Oct 27, 2019 1:30 AMTue Oct 29, 2019 3:00 PM18:3004:3014:00
6Sun Oct 27, 2019 1:30 AMTue Oct 29, 2019 10:00 AM13:30 03:30
7
1b
Cell Formulas
RangeFormula
B2B2=WORKDAY(A2,2)+F2+F6
C2,C4:C6C2=IF(WORKDAY(A2,2)>5,(NETWORKDAYS(A2,B2,0)-1)*$F$4+MIN(MOD(B2,1)-$F$2,$F$4),"???")
D4:D6D4=IF(C4>$F$5,C4-$F$5,"")
F4,F6F4=F3-F2
 

Watch MrExcel Video

Forum statistics

Threads
1,130,009
Messages
5,639,527
Members
417,094
Latest member
rh075192

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
Top