Excel DATEDIF function

Summary

The Excel DATEDIF function calculates the difference between two dates in years, months, or days. Originating from Lotus 1-2-3, DATEDIF is considered a “compatibility” function but remains useful for various calculations in Excel. It allows for precise age calculations, time spans, and other date-related differences, even though it’s not as prominently featured in Excel’s interface as other functions.
Syntax
				
					=DATEDIF(start_date, end_date, unit)
				
			
  • start_date: The beginning date in Excel date serial number format.
  • end_date: The ending date in Excel date serial number format.
  • unit: The time unit to use (“y” for years, “m” for months, “d” for days, “md” for day difference ignoring months and years, “ym” for month difference ignoring years, “yd” for day difference ignoring years).
Return value
A number representing the time between the two dates according to the specified unit.

How to use

To calculate the time difference between two dates, input the start and end dates along with the desired time unit. The function will return the difference as a number based on the unit specified. DATEDIF is particularly useful in scenarios where calculating age, tenure, or periods between events is necessary.

Examples

Simple DATEDIF

Calculating Tenure in Years: To calculate the number of complete years someone has worked based on their start date:

				
					=DATEDIF(A1, TODAY(), "y")
				
			
This formula calculates the number of complete years from the start date in A1 to today’s date.
DATEDIF and IF
Conditional Age-based Messaging: To display a message based on someone’s age:
				
					=IF(DATEDIF(B1, TODAY(), "y") >= 18, "Adult", "Minor")
				
			
This formula calculates the age based on the birth date in B1 and displays “Adult” if 18 or older, or “Minor” if under 18.
DATEDIF with Months
Project Phase Duration in Months: To calculate how many complete months a project phase lasted:
				
					=DATEDIF(C1, D1, "m")
				
			
Assuming the start date is in C1 and the end date is in D1, this formula calculates the total number of complete months between the two dates.
DATEDIF for Days
Days Until Event: To find out how many days are left until a future event:
				
					=DATEDIF(TODAY(), E1, "d")
				
			
This calculates the number of days from today until the event date in E1.
DATEDIF for Dynamic Age Calculation
Dynamic Age Calculation: To continually update someone’s age:
				
					=DATEDIF(F1, TODAY(), "y") & " years, " & DATEDIF(F1, TODAY(), "ym") & " months"
				
			
This formula calculates the age in years and months from a birthdate in F1, updating dynamically as time progresses.

Additional Notes

  • While DATEDIF is powerful, it’s often overlooked due to its absence from Excel’s function autocomplete and some versions of official documentation.
  • Pay attention to the order of start_date and end_date; providing them in reverse will result in an error.
  • Be cautious with the “md” unit as it may yield unexpected results. Microsoft recommends using other units for more consistent outcomes.
  • The function was introduced in Excel 2013 and is available in later versions. For compatibility with earlier versions, consider alternate methods for date difference calculations.

Related Functions

Excel DAYS360 function

The Excel DAYS360 function calculates days between dates using a 360-day year, ideal for financial and accounting calculations.

Excel NETWORKDAYS function

The Excel NETWORKDAYS function calculates working days between two dates, excluding weekends, useful for project timelines.

Excel YEARFRAC function

The Excel YEARFRAC function calculates the fraction of the year between two dates, essential for financial calculations.

Content Navigation