calulate time from two different dates and times without non working hours

[krifenbery]

I'm trying to calculate the total elapsed time from two different dates and times, however I need to subtract out 2am-5am since no work is going on. Please see example below:
A2 Date Start 12/01/13
B2 Time Start 1:00
C2 Date End 12/04/13
D2 Time End 18:00
E2 Time Elapsed (hrs) ??? I know that =((C2+D2)-(A2+B2))*24 will work except for subtracting out the hrs of 2am to 5am. Please help.
Kyle

 

[barry houdini]

Hello Kyle, welcome to MrExcel

I assume all days are workdays? If so then try this formula for elapsed working hours

=(C2+D2-A2-B2)*24-(C2-A2)*3-MEDIAN(D2*24,2,5)+MEDIAN(B2*24,2,5)

format result cell as general or number and you should get 77 for your example

 

[krifenbery]

Thanks Barry, you rock. Works perfect.