Total logged in hours (overlapping days)

[syed faheemuddin]

Please help me with the formula for total logged in hours for the below mentioned criterias:
1) Some Agents may not have logged in for the day.
2) Its a Overlapping Shift: Starting from 22:00 ending at next day morning 09:00.
3) All times are in 24 hrs format.
4) Need a formula which gives me blank if either the logged-in or logged-out time is not mentioned.

 

[Somendra Misra]

Hi Syed Faheemuddin,

Considering your logged in time in B2 & logged out time in B3. Use below formula in B4.
=IF(OR(B2="",B3=""),"",MOD(B3-B2,1)*24)

Advise if any issue.

Regards!