Converting date format in formulas in Excel

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

My formulae weren't working because I'm using a French Excel version.

Now that I've translated it, it works.