Calculating Time Difference

  • Thread starter Thread starter Alan
  • Start date Start date
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
 
=IF(A2<A1,1+A2-A1,A2-A1)

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

Jerry
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Determine if night shift by start & end time 2
Subtract times? 2
Length of time from date/time custom format 2
Difference of time 3
HELP! IF Statements Comparing Date/Time Entries (yyyy-mm-dd hh:mm 1
Time remaining until event 2
Start Time to End Time... Minutes On Each Hour... How to distribute proportionately? 5
Number of hours between now and selected date 5

Back
Top