time diffrence with 24 hour clock

[Guest]

can you tell me the formula for telling the time diffrence between 2 times
when the either time may appear after midnight, without the need for a 48hr
clock

time due - 23:00 time arrived - 01:00 (2 hours)
time due - 01:00 time arrived - 23:00 (blank) but using a 24 hr clock this
shows as 22 ours late when in fact is 2 hours early

thanks

 

[David Biddulph]

You'll either have to tell it the date as well as the time, or you're going
to have to decide where you draw the line between something being late for
one day and being early for the following day.

You could draw that line at 12 hours.
=MOD(B1-A1+0.5,1)-0.5

To display negative times you may have to use the 1904 date system (Tools/
Options/ Calculation)

 

[SteveW]

Just, assuming 2 times First and Last and no dates

=if( Last>First, Last-First, 1+First-Last)

Steve

 

[Guest]

Try this:
A1=23:00 and B1=1:00

=IF(A1>B1,(B1-A1+1)*24,"")