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.

I have two dates for eg. 12/02/2001 and 22/04/2001. I want to get the difference between the two in days. But when I try = A2-A1 is gives me #VALUE! On the other hand, =A1+1 works by adding 1 day. However, I want the difference of the two dates. How do I fix this?

share|improve this question
3  
are you sure that excel is set to UK dates? 22/04/2001 is not a valid date in the US, and excel seems to like US dates for almost everything –  Sean Cheshire Sep 5 '12 at 15:49
1  
I agree with Sean, if A1+1 works then presumably A1 is a valid date but A2 isn't - check by using ISNUMBER(A2) - if A2 is a date you get TRUE, I expect you'll get FALSE, where do the dates come from? –  barry houdini Sep 5 '12 at 20:18

1 Answer 1

Try

=DATEDIF(Date1, Date2, Interval)

Where:
Date1 is the first date,
Date2 is the second date,
Interval is the interval type to return.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.

For example

  • Date1: 1-Jan-2007
  • Date2: 10-Jan-2007
  • Interval: d
  • Result: 9

Source

Or, use DATEVALUE()

=DATEVALUE("9/29/2012")-DATEVALUE("9/10/2012")

Source

share|improve this answer
    
There are bugs in DATEDIF since Excel 2007 SP2 (I am not sure if it is fixed yet) esp when the interval parameter is "md". The function is not properly documented anyway; so if you are working on important data you may want to avoid using DATEDIF. Ref answers.microsoft.com/en-us/office/forum/office_2007-excel/… –  Kenneth L Sep 4 at 1:49

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.