Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

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

Now that I've translated it, it works.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Not sure why this was downvoted. Its a simple answer which works. –  Vivek Aseeja Jan 23 '12 at 20:08
    
Thanks @vivekian2, the downvote puzzled me too. –  Mike Fitzpatrick Jan 23 '12 at 23:34

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.

share|improve this answer
    
While complicated, I like that this answer gives the answer OP was seeking. +1 for you. –  user3463 Dec 30 '10 at 2:23

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.

share|improve this answer
    
The result of the above operation is 43943:59 which means that the difference is 43943 hours and 59 minutes. –  Mehper C. Palavuzlar Dec 29 '10 at 13:00
    
By the way, I just realized you are using Excel 2003, so you can check if you have DateDif function. –  Mehper C. Palavuzlar Dec 29 '10 at 13:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.