Calculating Time Difference

A

Alan

Hi,

Searched previous postings but can't find an answer to my
problem - hope someone can help. I have two columns both
in hh:mm format; first shows a start time and second shows
a stop time. I want a third column to show the difference
in hours and minutes between the two times - if I can't
have this I will accept difference in minutes only between
the two times. Best I have come up with is the calculation
=ABS(a2-a1) and custom format the cell to show minutes
only; [mm]. There seems to be a problem with this - the
calculation does not always return the expected time
difference; for example a start time of 23:00 and finish
time of 06:00 (next day!) returns the equivalent of 17
hours time difference not 7 hours as expected! Struggling
to get the right formula - am I going about this the right
way?

TIA
 
J

Jerry W. Lewis

=IF(A2<A1,1+A2-A1,A2-A1)

If you would prefer hours and minutes, use [hh]:mm custom format.

Jerry
 
A

Alan

Thanks Jerry, works perfectly.
-----Original Message-----
=IF(A2<A1,1+A2-A1,A2-A1)

If you would prefer hours and minutes, use [hh]:mm custom format.

Jerry
Hi,

Searched previous postings but can't find an answer to my
problem - hope someone can help. I have two columns both
in hh:mm format; first shows a start time and second shows
a stop time. I want a third column to show the difference
in hours and minutes between the two times - if I can't
have this I will accept difference in minutes only between
the two times. Best I have come up with is the calculation
=ABS(a2-a1) and custom format the cell to show minutes
only; [mm]. There seems to be a problem with this - the
calculation does not always return the expected time
difference; for example a start time of 23:00 and finish
time of 06:00 (next day!) returns the equivalent of 17
hours time difference not 7 hours as expected! Struggling
to get the right formula - am I going about this the right
way?
Click to expand...

.
Click to expand...
 
Top