Difference between times. Start time: 11:30PM End time: 5 AM

[Surrey]

I need to calculate the difference between times. The tricky part is that the
start time is: 11:30PM and end time is: 5:00AM. Thanks for your help!

 

[Mike H]

Try this,

Works for times over midnight and times on the same day

=(B1-A1+(B1<A1))*24

Mike

 

[Bernard Liengme]

=(B1-A1) will work unless you pass through midnight
=(B1-A1)+(B1<A1) will correctly add 1 (one day, that is, 24 hours) when B1
appears to be before A1 (ie you passed through midnight)
Try it with some of your data and tell me if you are happy with it
best wishes

 

[JE McGimpsey]

Since XL stores times as fractional days, you can add 1 day if the span
crosses midnight. One way is to check if the end time is less than the
beginning time and use the fact that XL converts TRUE to 1 and FALSE to
0:

A1: 11:30PM
B1: 5:00AM
C1: =B1 - A1 + (B1<A1)


An alternative:

C1: =MOD(B1-A1,1)


In either case, format C1 as a time.

 

[David Biddulph]

=MOD(B1-A1,1), but make sure you enter the times correctly.
11:30PM and 5:00AM would probably be interpreted as text;
11:30 PM and 5:00 AM are probably what you want.

 

[Codeman]

You could enter time similar to military time. 11.50 (11:30 am) start and
17.00 (5:00 pm) as end time. That is if you are familiar with decimal half
hours and quarter hours. But the other responses may already have the fix
for you.