DATEDIF

DATEDIF(start_date, end_date, unit)

Returns the number of days, months or years 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"
"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)
"m","M" = number of months (whole months) between "start_date" and "end_date"
"ym","YM" = number of months between "start_date" and "end_date" (ignoring days and years)
"y","Y" = number of years (whole years) between "start_date" and "end_date"

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". (Rows 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 DATEDIFFERENCE - User Defined Function as an alternative.
* You can use this Formula to return the number of weeks between two dates.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 A
1=DATEDIF("01 Jan 2022", "04 Jan 2022", "D") = 3
2=DATEDIF("01 Jul 2022", "01 Aug 2023", "d") = 396
3=DATEDIF("01 Jul 2022", "01 Aug 2022", "M") = 1
4=DATEDIF("20 Jun 2022", "01 Aug 2023", "m") = 13
5=DATEDIF("01 Jul 2022", "01 Aug 2022", "Y") = 0
6=DATEDIF("01 Jan 2022", "01 Jan 2024", "y") = 2
7=DATEDIF("01 Jan 2022", "02 Dec 2023", "MD") = 1
8=DATEDIF("28 Feb 2022", "01 Mar 2023", "MD") = 1
9=DATEDIF("28 Feb 2022", "01 Mar 2024", "MD") = 2
10=DATEDIF("01 Jan 2022", "01 Jul 2022", "YD") = 181
11=DATEDIF("28 Feb 2022", "01 Mar 2023", "YD") = 1
12=DATEDIF("28 Feb 2022", "01 Mar 2024", "YD") = 2
13=DATEDIF("01 Mar 2022", "01 Aug 2024", "YM") = 5
14=DATEDIF("28 Feb 2022", "01 Mar 2023", "YM") = 0
15=DATEDIF("28 Feb 2022", "01 Mar 2024", "YM") = 0
16=DATEDIF("28 Feb 2022", "01 Mar 2023", "YD") = 1
17=DATEDIF("28 Feb 2022", "01 Mar 2024", "YD") = 2
18=DATEDIF("01 Jan 2022", "06 Mar 2023", "YD") = 64
19=DATEDIF("01 Jan 2022", "06 Mar 2022", "YD") = 64
20=DATEDIF("invalid date", "15 Aug 2022", "MD") = #VALUE!

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

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