Check Sheet Logo
Check Sheet

DATEDIF

The DATEDIF function in Google Sheets calculates the difference between two dates using a specified unit of measurement. This function is commonly used to calculate the length of time between two dates, such as the number of days, months, or years. The function is particularly useful when working with financial or project planning data.

Usage

Use the DATEDIF formula with the syntax shown below, it has 3 required parameters:

=DATEDIF(start_date, end_date, unit)
Parameters:
  1. start_date (required):
    The starting date of the time period to calculate. This must be a valid date in Google Sheets, such as a cell reference or a date enclosed in quotation marks.
  2. end_date (required):
    The ending date of the time period to calculate. This must be a valid date in Google Sheets, such as a cell reference or a date enclosed in quotation marks.
  3. unit (required):
    The unit of measurement to use when calculating the time period. This must be one of the following values: "D" for days, "M" for months, or "Y" for years.

Examples

Here are a few example use cases that explain how to use the DATEDIF formula in Google Sheets.

Calculate age

The DATEDIF function can be used to calculate a person's age based on their birthdate and the current date. By specifying the unit as "Y", the function will return the number of years between the two dates.

Calculate project duration

When planning a project, the DATEDIF function can be used to calculate the length of time between the start and end dates. By using different units, such as "M" for months or "D" for days, the function can provide different levels of detail in the calculation.

Calculate billing period

Invoicing and billing systems can use the DATEDIF function to calculate the length of time between billing periods, such as the number of days between two invoices. By specifying the unit as "D", the function will return the number of days between the two dates.

Common Mistakes

DATEDIF not working? Here are some common mistakes people make when using the DATEDIF Google Sheets Formula:

Incorrect date format

The formula won't work if the date format is incorrect. Ensure that the dates are entered in a valid format, such as 'dd/mm/yyyy'.

Wrong order of dates

The start date should be entered before the end date in the formula. Check that the dates are entered in the correct order.

Incorrect unit

The third argument in the formula should be one of the valid units: 'Y', 'M', or 'D'. Ensure that the correct unit is used.

Related Formulas

The following functions are similar to DATEDIF or are often used with it in a formula:

Learn More

You can learn more about the DATEDIF Google Sheets function on Google Support.