Calculate Time Between Two Times In Excel That Cross Midnight?

[elrayo79]

Okay, so I have scoured the Forum's and the web in general. There is a lot of info on how to calculate time but this is not addressed. I need to calculate hours worked between 9:00 PM - 12:00 AM when the employee works over midnight. Example: Start 9:00 AM Stop 2:30 AM should equal 3 because the employee worked between 9:00 PM and Midnight. Right now I have it is incorrectly working with

Start 6:30 AM Stop 2:30 AM works with = IF (End Time > 9:00 PM, End Time - 9:00 PM, 11:59:59 PM - 9:00 PM) * 24
Start 7:30 AM Stop 8:30 PM does not work with the same formula because the False triggers the 11:59:59 PM - 9:00 PM because the End time is not greater.

Using = IF (End Time > 9:00 PM, End Time - 9:00 PM, 0) * 24 of course does not work either because Excel treats time worked the next day as Less than the end time because it does not realize that it is the next day . Any help would be greatly appreciated!

V/R,

elrayo79

 

[steve the fish]

Hi. Lets say you have start time in A1 and end time in B1:

=((B1 < A1)+B1-A1)*24<a1)+b1-a1)*24< html=""></a1)+b1-a1)*24<>

 

[elrayo79]

Hello! That works with a straight time to time calculation but I am doing a separate calculation for the hours between 9 PM - 12 AM to calculate premium pay when the employee works from 9 AM - 3 AM for instance. The calc needs to 3 hours because they are eligible for the premium pay between 9-12. So, I am trying to do a separate calculation with a max value of 3 hours if the employee worked between 9 PM and 12 AM.

 

[steve the fish]

Oh ok. Try it like this then:

=MAX(0,24*(IF(B1>=A1,B1,1)-MAX(A1,21/24)))

Format to general/ number.

 

[elrayo79]

That works! Wow. Pretty simple approach that I was not even considering. Thank you for the assist with this!!!