Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 More…

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

Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. Please see the known issues section of this article for further details.



start_date   RequiredA date that represents the first, or starting date of a given period. Dates may be entered as text strings within quotation marks (for example, “2001/1/30”), as serial numbers (for example, 36921, which represents January 30, 2001, if you’re using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE(“2001/1/30”)).
end_date   RequiredA date that represents the last, or ending, date of the period.
Unit   The type of information that you want returned, where:UnitReturnsY“The number of complete years in the period.”M“The number of complete months in the period.”D“The number of days in the period.”MD“The difference between the days in start_date and end_date. The months and years of the dates are ignored.Important: We don’t recommend using the “MD” argument, as there are known limitations with it. See the known issues section below.”YM“The difference between the months in start_date and end_date. The days and years of the dates are ignored”YD“The difference between the days of start_date and end_date. The years of the dates are ignored.



Start_dateEnd_dateFormulaDescription (Result)
1/1/20011/1/2003=DATEDIF(Start_date,End_date,”Y”)Two complete years in the period (2)
6/1/20018/15/2002=DATEDIF(Start_date,End_date,”D”)440 days between June 1, 2001, and August 15, 2002 (440)
6/1/20018/15/2002=DATEDIF(Start_date,End_date,”YD”)75 days between June 1 and August 15, ignoring the years of the dates (75)

Known issues

The “MD” argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month, here is a workaround:=DATEDIF(D17,E17,"md") and result: 5

This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Here’s how it does this: First the DATE function creates the date, 5/1/2016. It creates it using the year in cell E17, and the month in cell E17. Then the 1 represents the first day of that month. The result for the DATE function is 5/1/2016. Then, we subtract that from the original end date in cell E17, which is 5/6/2016. 5/6/2016 minus 5/1/2016 is 5 days.

Leave a Reply

Your email address will not be published. Required fields are marked *

Open chat
ভর্তি/সার্টিফিকেট তথ্য জানতে ক্লিক করুন
Scan the code
আস সালামু আলাইকুম। বিস্তারিত জানতে/ কল করতে ক্লিক করুন Open Chat বাটনে