Difference between 2 times and dates

  • Thread starter Thread starter Stefan Buijs
  • Start date Start date
S

Stefan Buijs

Hello,

The following problem we have to deal with:

On a sheet we have at cell A1 date number one and cell B1 time in the format
(hh:mm) and at cell A2 date number two en cell B2 time in the format
(hh:mm).
Now we have to calculate the time difference between date en time 1 and 2 in
cell C1.

How can I get this done?

Stefan
 
Assuming these are all in the correct numeric (not text) formats, in C1 the
formula would be:
=(A2+B2)-(A1+B1)
However, the result will appear strange unless you format the result (will
show as a date, probably 01/00/1900 (US date format)). If you know you
always will have a result less than 24 hours and you want to see hours and
minutes, format the cell with short time format ("hh:mm"); if you need the
difference as a total number of hours or minutes and it might be >24 hours,
you will need to adjust the formula:
=24*((A2+B2)-(A1+B1)) to give number of hours
=24*60*((A2+B2)-(A1+B1)) to give the number of minutes
You can also use the Round, Int, and Mod functions to round these
appropriately or to separate out the hours and minutes.
 
To make life simple use the same cell to display the day and time
For instance you can type 01/01/05 10:00 in A1 and 02/01/05 11:00 in A2 then
in A3 just use the fomula = A2-A1. You will need to change the format of Cell
A3 to Time to one showing time in xx:xx:xx format or you can use custom
format like [h]:mm:ss. You will then see the time difference in hours,
minutes and seconds. Assuming you are in Europe with dd/mm/yy date system the
above formula and formatting will give you 25:00:00 as the answer since the
difference is 25 hours between 1st Jan 05 10:00 AM and 2nd Jan 05 11:00 AM.

Alok Joshi
 
Back
Top