DATEDIF(start_date, end_date, unit)

Returns the number of units between two dates.

start_dateThe date that represents the starting date of the period.
end_dateThe date that represents the finishing date of the period.
unitThe type of information you want returned:
"D" = number of days between "start_date" and "end_date"
"M" = number of months (whole months) between "start_date" and "end_date"
"Y" = number of years (whole years) between "start_date" and "end_date"
"MD" = number of days between "start_date" and "end_date" (ignoring months and years)
"YD" = number of days between "start_date" and "end_date" (ignoring years)
"YM" = number of months between "start_date" and "end_date" (ignoring days and years)

Remarks

* This function does not appear in the Function Wizard or the Cell AutoComplete.
* This function is only available for backwards compatibility and should never be used.
* This function looks very useful but it has quite a few bugs.
* For an illustrated example refer to the page under Advanced Functions.
* If "start_date" > "end_date", then #NUM! is returned.
* If "start_date" is not a valid date, then #VALUE! is returned.
* If "end_date" is not a valid date, then #VALUE! is returned.
* If "unit" is not D, M, Y, MD, YD or YM, then #NUM! is returned.
* The unit is not case sensitive (you can use: d, m, y, td, ym, md).
* If you are including the date directly then it must be enclosed in double quotes.
* If you are including the unit directly then it must be enclosed in double quotes.
* The following are leap years: 2000, 2004, 2008, 2012, 2016, 2020, 2024, 2028, 2032.
* When calculating leap years the year from the 'start_date' is used (and the year from the 'end_date' is ignored) when you are using yd, ym and md).
* You can use the DAYS function to returns the number of days between two dates.
* You can use the YEARFRAC function to return the number of years as a decimal between two dates.
* For the Microsoft documentation refer to support.office.com

 A
1=DATEDIF("01/01/2010","04/01/2010","D") = 3
2=DATEDIF("01/02/2010","01/03/2010","D") = 28
3=DATEDIF("01/02/2010","01/04/2010","D") = 59
4=DATEDIF("01/07/2010","01/08/2010","D") = 31
5=DATEDIF("01/07/2009","01/08/2010","D") = 396
6=DATEDIF("01/07/2010","29/07/2010","M") = 0
7=DATEDIF("01/07/2010","01/08/2010","M") = 1
8=DATEDIF("01/07/2009","01/08/2010","M") = 13
9=DATEDIF("20/06/2009","01/08/2010","M") = 13
10=DATEDIF("01/07/2010","01/08/2010","Y") = 0
11=DATEDIF("01/07/2009","01/08/2010","Y") = 1
12=DATEDIF("01/01/2009","30/12/2010","Y") = 1
13=DATEDIF("01/01/2009","01/01/2011","Y") = 2
14=DATEDIF("01/01/2010","01/02/2010","MD") = 0
15=DATEDIF("01/01/2010","01/07/2010","MD") = 0
16=DATEDIF("01/01/2009","02/12/2010","MD") = 1
17=DATEDIF("01/01/2009","07/12/2010","MD") = 6
18=DATEDIF("10/01/2009","25/12/2010","MD") = 15
19=DATEDIF("01/01/2009","31/12/2010","MD") = 30
20=DATEDIF("01/01/2000","10/07/2010","MD") = 9
21=DATEDIF("01/01/2008","01/01/2010","YD") = 0
22=DATEDIF("01/01/2010","02/01/2010","YD") = 1
23=DATEDIF("01/01/2010","01/02/2010","YD") = 31
24=DATEDIF("01/01/2010","01/07/2010","YD") = 181
25=DATEDIF("01/01/2009","02/12/2010","YD") = 335
26=DATEDIF("01/01/2009","07/12/2010","YD") = 340
27=DATEDIF("10/01/2009","25/12/2010","YD") = 349
28=DATEDIF("01/01/2009","31/12/2010","YD") = 364
29=DATEDIF("01/01/2000","10/07/2010","YD") = 191
30=DATEDIF("01/07/2008","01/07/2010","YM") = 0
31=DATEDIF("01/07/2008","01/08/2010","YM") = 1
32=DATEDIF("01/07/1950","01/08/2010","YM") = 1
33=DATEDIF("01/03/2008","01/08/2010","YM") = 5
34=DATEDIF("01/03/2008","01/08/2010","YM") = 5
35=DATEDIF("01/01/2008","01/07/2011","YM") = 6
36=DATEDIF("some text","2002/8/15","MD") = #VALUE!

1 - How many days are there between 1 Jan 2010 and 4 Jan 2010. The "start_date" is not included.
2 - How many days are there between 1 Feb 2010 and 1 Mar 2010.
3 - How many days are there between 1 Feb 2010 and 1 Apr 2010.
4 - How many days are there between 1 July 2010 and 1 Aug 2010.
5 - How many days are there between 1 July 2009 and 1 Aug 2010.
6 - 9 - the number of "whole" months between the start and end dates.
10 - 13 - the number of "whole years" between the start and end dates.
14 - How many days are there between 1 Jan 2010 and 1 Feb 2010 ignoring the months.
15 - 20 - the number of days between the start and end dates (ignoring the year components so assuming the year is the same).
21 - How many days are there between 1 Jan 2008 and 1 Jan 2010 ignoring the years.
22 - 29 - the number of days between the start and end dates (excluding both month and year components so assuming the month and the year are the same).
30 - How many months are there between 1 Jul 2008 and 1 Jul 2010 ignoring the years.
31 - 35 - the number of months between the start and end dates (ignoring the year components so assuming the year is the same).
36 - difference in words

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top