DATEDIFfunction 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.
- How to use
- Examples of using
DATEDIFformula not working?
- Similar formulas to
DATEDIF formula with the syntax shown below, it has 3 required parameters:
- 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.
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
DATEDIFformula in Google Sheets.
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.
DATEDIFnot working? Here are some common mistakes people make when using the
DATEDIFGoogle 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.
The third argument in the formula should be one of the valid units: 'Y', 'M', or 'D'. Ensure that the correct unit is used.
The following functions are similar to
DATEDIF or are often used with it in a formula:
You can learn more about the
DATEDIF Google Sheets function on Google Support.