formula to calculate time difference crossing midnight

D

ditorejax

In Excel I have been trying to find an easier way to calculate a time
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00

How would you formulate an equation to determine the duration of time or
differnce between the start and end time?
 
R

Richard Buttrey

In Excel I have been trying to find an easier way to calculate a time
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00

How would you formulate an equation to determine the duration of time or
differnce between the start and end time?
Click to expand...

One way which results in hours and decimal of an hour is

=IF((end-start)<0,(end-start)*24+24,(end-start)*24)

If you want to see hours and minutes you'd need to modify it to pick
up the decimal fraction and multiply it by 60.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
M

Marcelo

Hi,

one way is add 1 in 00:15:00

assuming that 23:50:00 is on A2 and 00:15:00 is on A3
=a3+1-a2

=00:25:00

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"ditorejax" escreveu:
 
S

Sloth

=(A2>A3)+A3-A2

1 only needs to be added if A2>A3 otherwise you get 24 extra hours when
times don't go over midnight. You will see the problem when you start
summing the times and if you format as [h]:mm:ss
 
Top