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","D" = number of days between "start_date" and "end_date"
"m","M" = number of months (whole months) between "start_date" and "end_date"
"y","Y" = number of years (whole years) between "start_date" and "end_date"
"md","MD" = number of days between "start_date" and "end_date" (ignoring months and years)
"yd","YD" = number of days between "start_date" and "end_date" (ignoring years)
"ym","YM" = number of months between "start_date" and "end_date" (ignoring days and years)

Remarks

* This function is only available for backwards compatibility and should never be used.
* For an illustrated example (and equivalent formulas) refer to the page under Advanced Functions.
* This function does not appear in the Function Wizard or the Cell AutoComplete.
* The accuracy of this function was improved in Excel 2010 although it still contains quite a few bugs.
* If "start_date" > "end_date", then #NUM! is returned. Example 20.
* 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 MD, then the year is taken from the "end_date".
* If "unit" is YD, then the year is sometimes taken from the "start_date" and other times taken from the "end_date". Examples 16, 17, 18 and 19.
* If "unit" is YD, then leap years may or may not be taken into account.
* If "unit" is YM, then the year is taken from the "start_date".
* If "unit" is not D, M, Y, MD, YD or YM, then #NUM! is returned.
* 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.
* 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.
* You can use the DATEUNITS - User Defined Function as an alternative.
* For the Microsoft documentation refer to support.office.com

 A
1=DATEDIF("01 Jan 2020","04 Jan 2020","D") = 3
2=DATEDIF("01 Jul 2019","01 Aug 2020","d") = 397
3=DATEDIF("01 Jul 2020","01 Aug 2020","M") = 1
4=DATEDIF("20 Jun 2019","01 Aug 2020","m") = 13
5=DATEDIF("01 Jul 2020","01 Aug 2020","Y") = 0
6=DATEDIF("01 Jan 2019","01 Jan 2021","y") = 2
7=DATEDIF("01 Jan 2019","02 Dec 2020","MD") = 1
8=DATEDIF("28 Feb 2018","01 Mar 2019","MD") = 1
9=DATEDIF("28 Feb 2018","01 Mar 2020","MD") = 2
10=DATEDIF("01 Jan 2020","01 Jul 2020","YD") = 182
11=DATEDIF("28 Feb 2018","01 Mar 2019","YD") = 1
12=DATEDIF("28 Feb 2018","01 Mar 2020","YD") = 2
13=DATEDIF("01 Mar 2018","01 Aug 2020","YM") = 5
14=DATEDIF("28 Feb 2018","01 Mar 2019","YM") = 0
15=DATEDIF("28 Feb 2018","01 Mar 2020","YM") = 0
16=DATEDIF("28 Feb 2018","01 Mar 2019","YD") = 1
17=DATEDIF("28 Feb 2018","01 Mar 2020","YD") = 2
18=DATEDIF("01 Jan 2019","06 Mar 2020","YD") = 64
19=DATEDIF("01 Jan 2020","06 Mar 2020","YD") = 65
20=DATEDIF("some text","15 Aug 2002","MD") = #VALUE!

1 - How many days are there between 1 Jan 2020 and 4 Jan 2020. The "start_date" is not included.
2 - How many days are there between 1 Jul 2019 and 1 Aug 2020.
3 - How many months are there between 1 Jul 2019 and 1 Aug 2020.
4 - How many months are there between 20 Jun 2019 and 1 Aug 2020.
5 - How many years are there between 1 Jul 2020 and 1 Aug 2020.
6 - How many years are there between 1 Jan 2019 and 1 Jan 2021.
7 - How many days are there between 01 Jan 2019 and 02 Dec 2020 (ignoring months and years).
8 - How many days are there between 28 Feb 2018 and 01 Mar 2019 (ignoring months and years).
9 - How many days are there between 28 Feb 2018 and 01 Mar 2020 (ignoring months and years).
10 - How many days are there between 01 Jan 2020 and 01 Jul 2020 (ignoring years).
11 - How many days are there between 28 Feb 2018 and 01 Mar 2019 (ignoring years)
12 - How many days are there between 28 Feb 2018 and 01 Mar 2020 (ignoring years).
13 - How many days are there between 01 Mar 2018 and 01 Aug 2020 (ignoring years).
14 - How many months are there between 28 Feb 2018 and 01 Mar 2019 (ignoring days and years).
15 - How many months are there between 28 Feb 2018 and 01 Mar 2020 (ignoring days and years).
16 - How many days are there between 28 Feb 2018 and 01 Mar 2019 (ignoring years).
17 - How many days are there between 28 Feb 2018 and 01 Mar 2020 (ignoring years).
18 - How many days are there between 01 Jan 2019 and 06 Mar 2020 (ignoring years).
19 - How many days are there between 01 Jan 2020 and 06 Mar 2020 (ignoring years).
20 - If the start_date is not a valid date, then #VALUE! is returned.

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