Difference between 2 times and dates

[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

 

[Guest]

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.

 

[Guest]

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