4

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?

EXCELMASTERZ
  • 41
  • 1
  • 1
  • 2
  • 8
    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 05 '12 at 15:49
  • 7
    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 05 '12 at 20:18
  • 1
    wrong! Excel will use the current locale settings, not US. Opening an Excel file created in German or French with `;` as function separator and `,` as decimal point in a UK or US locale will get you into trouble – phuclv Aug 04 '16 at 16:29
  • Related: [How to prevent Excel to use the OS regional settings for date patterns in formulas](http://superuser.com/q/730371/348859) – Engineer Toast Nov 11 '16 at 15:23
  • The format for the dates should be "Date". Dates entered in cells should be based on the selected format type (MM-DD-YY) – David Jan 19 '18 at 01:30

3 Answers3

1

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.

Mark Ransom
  • 2,130
  • 2
  • 20
  • 28
0

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

Dave
  • 25,297
  • 10
  • 57
  • 69
  • 1
    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 http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-to-change-number-of-days-to-yearsmonths-and/d711c71e-8b71-4311-b568-57b6b4eee504 – Kenneth L Sep 04 '14 at 01:49
0

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

lisa
  • 1