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.
answered Dec 30, 2017 at 6:13
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
DATEDIFsince Excel 2007 SP2 (I am not sure if it is fixed yet) esp when theintervalparameter 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