Excel Formulae Datedif

1. Age Calculation

Excel DATEDIF function

Summary

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

Note: Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly.

Purpose

Get days, months, or years between two dates

Return value

A number representing time between two dates

Syntax

=DATEDIF (start_date, end_date, unit)

Arguments

  • start_date - Start date in Excel date serial number format.
  • end_date - End date in Excel date serial number format.
  • unit - The time unit to use (years, months, or days).

Usage notes

The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but it works in all Excel versions since that time. As Chip Pearson says: DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation.

The DATEDIF function calculates the time between a start_date and an end_date in years, months, or days. The time unit to return is specified using the unit argument. Unit is supplied as text (upper or lower case), using any one of these values:

Note: DATEDIF with throw a #NUM error if start date is greater than the end date. If you are working with a more complex formula where start dates and end dates may be unknown, or out of bounds, you can trap the error with the IFERROR function.

DATEDIF formula examples

Next anniversary date

To calculate the next anniversary date, you can use a formula based on the EDATE and DATEDIF functions. In the example shown, the formula in D5 is: =EDATE(B5,(DATEDIF(B5,C5,"y")+1)*12) This formula will...

Excel formula: Next anniversary date
Excel formula: Get days between dates ignoring years

Get days between dates ignoring years

To calculate days between two dates, ignoring year values, use the DATEDIF function. In the example shown, the formula in D6 is: =DATEDIF(B6,C6,"yd") How this formula works The DATEDIF function can handle...

Excel formula: Get days, months, and years between dates

Get days, months, and years between dates

To calculate and display the time between dates in days, months, and years, you can use the a formula based on the DATEDIF function. In the example shown, the formula in D6 is: =DATEDIF(B6,C6,"y") &...

Excel formula: Get months between dates

Get months between dates

To calculate months between two dates as a whole number, you can use the DATEDIF function. In the example shown, the formula in D6 is: =DATEDIF(B6,C6,"m") Note that the DATEDIF automatically rounds down....

Related functions

Excel NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

Excel YEARFRAC Function

The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC for formulas that do things like like calculating age in years using a birthdate.

Excel DAYS360 Function

The Excel DAYS360 function returns the number of days between two dates based on a 360-day year. Calculations based on a 360-day year comes from certain accounting calculations where all 12 months are considered to have 30 days.

Good links

DATEDIF examples (Chip Pearson)

Excel NETWORKDAYS function
Excel YEARFRAC function
Excel DAYS360 function