2

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 5

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.

2
  • 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
1

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.

1
  • While complicated, I like that this answer gives the answer OP was seeking. +1 for you.
    – user3463
    Dec 30, 2010 at 2:23
1

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.

0

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

1
  • Not sure why this was downvoted. Its a simple answer which works.
    – vivekian2
    Jan 23, 2012 at 20:08
0

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

Now that I've translated it, it works.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .