diggerdidoodar
Board Regular
- Joined
- May 20, 2002
- Messages
- 59
Found this wonderful example of Net work hours between dates. I need to now know now how I can modify this formula to allow me to add a set time value of say 7 hrs to cell a6 so I get result 25/02/2019 07:00 etc etc. Hope that makes sense.
B2 = 2pm
A2 = 6am
A6 = 22/02/2019 08:00
B6 = 25/02/2019 10:10
$Start_Time $End_Time
6:00:00 AM 2:00:00 PM
Start Date End Date Net Work Hours (Decimal) Net Work Hours (H:mm)
22/02/2019 10:10 25/02/2019 10:10 8.00 08:00
22/02/2019 08:00 25/02/2019 10:10 =IF(OR($B$2<$A$2,B6<A6),0,(NETWORKDAYS(A6,B6,HolidayList)-(NETWORKDAYS(A6,A6,HolidayList)*IF(MOD(A6,1)>$B$2,1,(MAX($A$2,MOD(A6,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS(B6,B6,HolidayList)*IF(MOD(B6,1)<$A$2,1,($B$2-MIN($B$2,MOD(B6,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)
B2 = 2pm
A2 = 6am
A6 = 22/02/2019 08:00
B6 = 25/02/2019 10:10
$Start_Time $End_Time
6:00:00 AM 2:00:00 PM
Start Date End Date Net Work Hours (Decimal) Net Work Hours (H:mm)
22/02/2019 10:10 25/02/2019 10:10 8.00 08:00
22/02/2019 08:00 25/02/2019 10:10 =IF(OR($B$2<$A$2,B6<A6),0,(NETWORKDAYS(A6,B6,HolidayList)-(NETWORKDAYS(A6,A6,HolidayList)*IF(MOD(A6,1)>$B$2,1,(MAX($A$2,MOD(A6,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS(B6,B6,HolidayList)*IF(MOD(B6,1)<$A$2,1,($B$2-MIN($B$2,MOD(B6,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)
