Calc DATEDIF function

For a full list of available calculation functions, please read What calculation functions are supported.

Description

The DATEDIF function is a useful tool for calculating the difference between two dates in various units, such as days, months, or years. It helps you determine the duration between two dates easily.

Syntax

DATEDIF(startDate, endDate, unit)

The DATEDIF function syntax has the following arguments:

• startDate: This is the starting date of the period you want to measure. You input this as a DATE function expression, like '2020-01-01' for the 1st of January, 2020.
• endDate: This is the ending date of the period you want to measure. You also input this as a DATE function expression.
• unit: This specifies the unit of time you want to use for the difference. It’s a text string and can be one of the following values:
  • 'Y': Calculates the number of complete years between startDate and endDate.
  • 'M': Calculates the number of complete months between startDate and endDate.
  • 'D': Calculates the number of days between startDate and endDate.
  • 'MD': Calculates the difference in days between startDate and endDate, ignoring months and years.
  • 'YM': Calculates the difference in months between startDate and endDate, ignoring days and years.
  • 'YD': Calculates the difference in days between startDate and endDate, ignoring years.

Remarks

• What It Does: It calculates the difference between two dates based on the specified unit. For example, DATEDIF('2020-01-01', '2021-01-01', 'Y') would return 1, indicating one complete year between the two dates.
• Handling Negative Dates: If startDate is later than endDate, the function will return a negative value. For instance, DATEDIF('2021-01-01', '2020-01-01', 'Y') would return -1.
• Invalid Units: If you provide an invalid unit, the function will return an error. Make sure to use one of the specified text strings.

Example

Errors

For a full list of formula errors, please read Formula errors.

Related functions

• Calc DATE function
• Calc DATESUB function
• Calc DAY function
• Calc DAYS function
• Calc EDATE function