Need To Get Elapsed Time Between 2 Dates During Working Hours ( All Days Included )

[Ahmed101056]

Hi

I need to get the elapsed hours between 2 dates considering that working hours is from 8 AM To 4 PM
all days included ( no week ends or holidays )

example :
A1 : 11/11/2019 09:31 AM
B1 : current time { now() } will be used

 

[Ahmed101056]

sorry for bothering you again but somehow the equation gives inaccurate results ( 12 or above added to each result )

 

[Eric W]

Sorry, I transposed 2 of the cell values in the formula. I also realized that it would create incorrect results if the start and end date was the same (but different times). This version should fix both:

=IF(INT(A2)=INT(B2),(MEDIAN(MOD(B2,1),C2,D2)-MEDIAN(MOD(A2,1),C2,D2))*24,(INT(B2)-INT(A2)-1)*8+(D2-MEDIAN(MOD(A2,1),C2,D2)+MEDIAN(MOD(B2,1),C2,D2)-C2)*24)

I've tried it on all the examples so far, and it seems to work correctly, but try to validate it yourself on a few more examples.

 

[Ahmed101056]

sorry man for being late am tested the equation several times now and its working fine ,, Thank u very much

 

[Eric W]

No worries! Thanks for the update. I'm just glad to hear that it works for you.