Adding a number of working hours to networkdays

[Juliaafreeman]

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.

 

[Johndinho]

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]

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]

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
	A	B	C	D	E	F
1	Arrival	Completed	Completed	Excess		Loading time
2	Sun Oct 27, 2019 1:30 AM	Tue Oct 29, 2019 10:30 AM	14:00		Start	07:00 AM
3					End	05:30 PM
4	Sun Oct 27, 2019 1:30 AM	Wed Oct 30, 2019 3:00 PM	29:00	15:00	Hours	10:30
5	Sun Oct 27, 2019 1:30 AM	Tue Oct 29, 2019 3:00 PM	18:30	04:30		14:00
6	Sun Oct 27, 2019 1:30 AM	Tue Oct 29, 2019 10:00 AM	13:30			03:30
7
1b

Cell Formulas
Range	Formula
B2	B2	=WORKDAY(A2,2)+F2+F6
C2,C4:C6	C2	=IF(WORKDAY(A2,2)>5,(NETWORKDAYS(A2,B2,0)-1)*$F$4+MIN(MOD(B2,1)-$F$2,$F$4),"???")
D4:D6	D4	=IF(C4>$F$5,C4-$F$5,"")
F4,F6	F4	=F3-F2