Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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 – SeanC Sep 5 '12 at 15:49
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

Also check that the data type for your equation is general or numeric. Had similar issues today and changing to "general" fixed everything.

share|improve this answer


=DATEDIF(Date1, Date2, Interval)

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





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… – Kenneth L Sep 4 '14 at 1:49

Your Answer


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.