# DATEDIF

DATEDIF(start_date, end_date, unit)

Returns the number of days, months or years between two dates.

 start_date The date that represents the starting date of the period. end_date The date that represents the finishing date of the period. unit The 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