I'm trying to calculate elapsed time in hours from a start date/time to an end date/time.
Work hours are M-F, 6AM - 6PM.
Below is the formula I'm using.
=(NETWORKDAYS(B3,C3)-1)*("18:00"-"6:00")+MOD(C3,1)-MOD(B3,1)
The problem I'm having is if the start day is after business hours or on Saturday or Sunday, The hours don't calculate correctly.
I need your help with a formula correction that will resolve the scenarios below in RED
<tbody>
</tbody>
Thanks much for your assistance in advance.
Work hours are M-F, 6AM - 6PM.
Below is the formula I'm using.
=(NETWORKDAYS(B3,C3)-1)*("18:00"-"6:00")+MOD(C3,1)-MOD(B3,1)
The problem I'm having is if the start day is after business hours or on Saturday or Sunday, The hours don't calculate correctly.
I need your help with a formula correction that will resolve the scenarios below in RED
Start Date | End Date | Elapsed Time | Comment | Correct Value | ||
<tbody> </tbody> |
<tbody> </tbody> | 2:00 | Incorrect value | 11:30 hrs | ||
<tbody> </tbody> |
<tbody> </tbody> | 11:00 | Incorrect Value | 11:30 hrs | ||
<tbody> </tbody> |
<tbody> </tbody> | 30:00 | Correct | 30:00 hrs | ||
<tbody>
</tbody>
Thanks much for your assistance in advance.