Function: DATEDIF

by | Jun 16, 2019 | Excel Formulas

datedif excel

The DATEDIF Excel function is a Date & Time formula that calculates and returns the number of days, months, and years difference between two dates. The DATEDIF function has been derived from Lotus 1-2-3 for compatibility, and is still kept in Excel for backwards compatibility. In this guide, we’re going to show you how to use the DATEDIF Excel function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

DATEDIF Excel Syntax

DATEDIF(start_date, end_date, unit)

Arguments

start_date The start date. You can use a valid text or date returned by a formula.
end_date The end date. You can use a valid text or date returned by a formula.
unit A string that represents the unit of the return value. See the table below for available options.

Unit Strings

Unit Returns
"D" The number of full days between dates
"M" The number of full months between dates
"Y" The number of full years between dates
"MD" The difference between days, ignoring months and years
Microsoft doesn't recommend using the "MD" argument, because it "may result in a negative number, a zero, or an inaccurate result".
"YM" The difference between months, ignoring days and years
"YD" The difference between days, ignoring years

 

Example

All arguments of the DATEDIF function are required. Enter a start_date, and an end date that is after the start_date, and unit to determine the vale to be returned.

The start_date and end_date arguments can be a string, a serial number, or a calculated date by a formula. Here is an example:

=DATEDIF("11/29/2018","04/23/2026","D")

=DATEDIF(43433,46135,"D")

=DATEDIF(DATE(2018,11,29),DATE(2026,4,23),"D")

Download Workbook

Tips

  • DATEDIF function is kept for Lotus 1-2-3 compatibility. However, Excel may not continue supporting it indefinitely.
  • The unit argument is not case-sensitive. Either "Y" or "y" is valid.
  • More examples:
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.

Issues

  • You will get a #NUM error if the start_date is greater than the end_date.
  • Using "MD" string for the unit argument may give an inaccurate result.