DATEDIF Function in Google Sheets: Explained

In this article, you will learn how to use the DATEDIF function in Google Sheets.

What is the DATEDIF formula in Google Sheets?

The DATEDIF formula in Google Sheets is a function that allows you to calculate the difference between two dates, providing the result in various units such as days, months, or years. This formula is particularly useful when you need to measure the time span between two events or dates.

How to use the DATEDIF function in Google Sheets?

To use the DATEDIF function in Google Sheets, follow these steps:

1. Click on the cell where you want to display the difference between dates.
2. Type “=DATEDIF” or go to the menu bar and navigate to "Insert" ➝ "Function" ➝ "Date" ➝ "DATEDIF"
3. Enter the start date, end date, and the unit of measurement as arguments, separated by commas.
4. Close the parenthesis and press the "Enter" key.

The general syntax of the DATEDIF formula is as follows:

=DATEDIF(start_date, end_date, unit)

start_date: The initial date for the calculation.

end_date: The final date for the calculation.

unit: The unit of measurement abbreviated (e.g., "D" for days, "M" for months, "Y" for years).

Let’s see an example. Consider you have a project with a given start date and an estimated end date and you want to calculate how much time it’s going to take to complete it in days, months, and years:

• # Days

‍

The resulting value can be seen in cell C8, and the formula in D8. In this example, we are working with cell references, where the Start Date of the project can be seen in cell B4 (format DD/MM/YYYY), and the end date in cell C4 (format DD/MM/YYYY). Another way  to do this could be using the DATE function directly inside the DATEDIFF formula as follows:

=DATEDIF(DATE(2022, 5, 5), DATE(2023, 12, 25), "D")

The outcome shows that the project will take 599 days to be completed.

‍

• # Months

‍

The resulting value can be seen in cell C9, and the formula in D9. As the  previous example, we are using cell references to define the start date (cell B4) and end date (cell C4).

‍

The outcome shows that the project will take 19 months to be completed.

‍

• # Years

‍

The resulting value can be seen in cell C10, and the formula in D10. Once again, we are using cell references to define the start date (cell B4) and end date (cell C4).

‍

The outcome shows that the project will take 1 year to be completed.

‍

It's important to understand that the DATEDIFF function always provides a whole number as its result. Consider this example: if the end date is set to 05/05/2023, the number of years returned would still be 1. However, if the end date is adjusted to 04/05/2023, the result would be 0, indicating that a full year hasn't elapsed as the end date did not complete the year.

Additional Tips:

1. The arguments for start_date and end_date should be valid date values; otherwise, the formula returns an error.

2. The unit parameter can be "days," "months," or "years," depending on the desired result.

3. Google Sheets automatically adjusts the calculation when dates are outside the standard month or day ranges.

4. Ensure that the start_date is earlier than the end_date to get accurate results.

5. You can use cell references or direct date values as arguments in the DATEDIF function.