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?
3 Answers
It seems that A1
is being interpreted properly as a date but A2
is not. Make sure your locale is set to interpret dates in the European style dd/mm/yy instead of the US mm/dd/yy.
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
Or, use DATEVALUE()
=DATEVALUE("9/29/2012")-DATEVALUE("9/10/2012")
-
1There are bugs in
DATEDIF
since Excel 2007 SP2 (I am not sure if it is fixed yet) esp when theinterval
parameter is "md". The function is not properly documented anyway; so if you are working on important data you may want to avoid usingDATEDIF
. Ref answers.microsoft.com/en-us/office/forum/office_2007-excel/… Sep 4, 2014 at 1:49
Also check that the data type for your equation is general or numeric. Had similar issues today and changing to "general" fixed everything.
;
as function separator and,
as decimal point in a UK or US locale will get you into trouble