what is the formula which would enable me to calculate the difference between 2 dates and 2 times? Example: In the same cell, I have 01 January 2005 07:30 and in a second cell i would have 06 January 2010 19:29. The answer should be 1832 days and 11h59 minutes if i'm not mistaken. Can somebody help me?
5 Answers
Let's say A1 is 01 January 2005 07:30
and A2 is 06 January 2010 19:29
. Go to an empty cell, say A3, and enter the following formula:
=A2-A1
Now, right click on cell A3 and select "Format Cells...". Under the "Number" tab, select "Custom". Input the following value inside the "Type" box:
[hh]:mm
Now you can see the difference in hours and minutes. I didn't try making it display days, hours and minutes together, but you can play with the type variable to achieve it.
In the earlier versions of Excel, there was a function called DateDif()
to achieve exactly what you want, but it doesn't seem to take place in Excel 2007 and Excel 2010.
-
The result of the above operation is
43943:59
which means that the difference is 43943 hours and 59 minutes. Dec 29, 2010 at 13:00 -
By the way, I just realized you are using Excel 2003, so you can check if you have
DateDif
function. Dec 29, 2010 at 13:06
I probably over complicated this answer because the formula is ridiculously long, but it works.
Assuming your start date is in A1 and your end date is in B1:
=(INT(B1-A1)) & " Days, " & (INT((B1-A1-INT(B1-A1))*24)) &" Hours, "& ROUND(((((B1-A1-INT(B1-A1))*24)-(INT((B1-A1-INT(B1-A1))*24)))*60),0) &" Minutes"
The result I get is: 1831 Days, 11 Hours, 59 Minutes
It is different from the 1832 days you expected because it is exclusive of the end date. If you want it to be inclusive change the formula to:
=(INT(B1-A1)+1) & " Days, " & (INT((B1-A1-INT(B1-A1))*24)) &" Hours, "& ROUND(((((B1-A1-INT(B1-A1))*24)-(INT((B1-A1-INT(B1-A1))*24)))*60),0) &" Minutes"
I'd prefer an easier answer, but I can't think of one right now. Hopefully someone will save the day with a more efficient/elegant solution.
-
While complicated, I like that this answer gives the answer OP was seeking. +1 for you.– user3463Dec 30, 2010 at 2:23
As Mehper mentioned, simple subtraction will yield the result 1831.49930555555.
The numbers before the decimal place are days, and the numbers after represent the hours and mins in fraction of an hour.
Subtract the number of days to get the remaining fraction, i.e. 1831.49930555555 - 1831 to get 0.49930555555
To be certain, you could use the following functions on the remaining fraction:
hour(CELL)
to get the exact hours, and...
minute(CELL)
to get the exact minutes...
Wolfram Alpha is pretty good for such stuff too.
Assuming the dates are in A1 and A2:
=INT(A2-A1)&" days "&TEXT(A2-A1-INT(A2-A1), "hh:mm")
yields:
1831 days 11:59
My formulae weren't working because I'm using a French Excel version.
Now that I've translated it, it works.