DATEDIF in Google Sheets: Formula & Examples

If you want to find the difference between two dates in Google Sheets, you can subtract the earlier date from the later one to get the difference in days. However, if you want the result in months or years, you’ll have to perform additional calculations. Instead, you can use the DATEDIF function and choose whether you want the result in days, months, or years. You can even get the remainder in days or months after dividing by a larger unit.

In this guide, you will learn about the DATEDIF function in Google Sheets. After reviewing the function syntax and the different ‘unit’ options, you will learn how to use the function to determine the difference between two dates in days, months, and years. You will also learn how to use the remainder ‘unit’ options to get the remainder of the difference in months or days after dividing by years or months.

DATEDIF Formula in Google Sheets

The DATEDIF function in Google Sheets has three required parameters: start date, end date, and unit.

=DATEDIF(start_date, end_date, unit)

• start_date: the start date for your calculation. It can be a reference to a cell containing a date data type, a function that returns a date, or a number.
• end_date: the end date for your calculation. It can be a reference to a cell containing a date data type, a function that returns a date, or a number.
• unit: the unit of time to be used: years, months, days. Acceptable values include “Y”, “M”, “D”, “YM”, “YD”, “MD”.

How to Use DATEDIF in Google Sheets?

Follow the steps below to get the difference between a past date and today’s date.

1. 1. In an empty cell, type the DATEDIF function.

1. 2. Type the start date between quotation marks followed by a comma, as shown below.

1. 3. Type the TODAY function followed by a comma.

1. 4. For the last parameter, specify the units you want for the result using the format seen in the previous section. For this example, I will use “D” for days.

1. 5. Close the parenthesis and press ‘Enter’ to see the results.

Examples: How Do I Find the Difference Between Two Dates?

Below, you have examples showing the other unit options when using the DATEDIF function: months and years. After that, you will learn how to use “YM”, “YD”, “MD”, which are a little strange since they return remainders. By using TODAY as the end date, you ensure that the difference is updated with every day that passes. If you have a specific end date, use that date instead of the TODAY function.

Difference in Months

For this example, I will find the difference between the same dates as in the previous example, but in months instead of days.

1. 1. Type the past date into a cell. In another cell, type the DATEDIF function.

1. 2. Select the cell with the start date as the first parameter and add a comma after it.

1. 3. Use the TODAY() function as the second parameter.

1. 4. For the third parameter, specify the unit between quotation marks. In this case, I want the answer in months, so I’ll use “M”.

1. 5. Close the parenthesis and press ‘Enter’ to see the result.

Difference in Years

For this example, I will find the difference in years between the same dates as in the previous example.

1. 1. In an empty cell, type in the DATEDIF function.

1. 2. Select the cell with the start date as the first parameter and add a comma after it.

1. 3. Use the TODAY function as the second parameter.

1. 4. For the third parameter, specify the unit between quotation marks. In this case, I want the answer in years, so I’ll use “Y”.

1. 5. Close the parenthesis and press ‘Enter’ to see the result.

Difference in Special Units

There are three unit formats that work a little differently than you might expect. They return the remainder in one unit after dividing by a larger unit. For example, “YM” returns the remainder in months after dividing by years. “YD” and “MD” return the remainder in days after dividing by years and months, respectively.

In the example below, I have included the results of the previous three examples for easier comparison.

1. 1. Type the DATEDIF function and select the cell with the start date.

1. 2. Add the TODAY function as the second parameter.

1. 3. Add “YM” as the third parameter, close the parenthesis, and press ‘Enter’.

1. 4. As you can see, the result is 11 months. There are 2+ years between the dates, so after dividing by years, the remainder is 11 months.

1. 5. Repeat steps 1 to 3, but use “YD” as the unit parameter. After dividing by years, the remainder in days is 345.

1. 6. Finally, repeat steps 1 to 3 and use “MD” as the unit parameter. After dividing by months, the remainder is

What if DATEDIF Does Not Work?

If the DATEDIF function isn’t working, there are a few things you should check:

• The start date should be earlier than the end date.
• The start and end dates should be enclosed in quotation marks or be cell references.
• The dates are in a valid date format.
• The unit parameter should be enclosed in quotation marks or be a cell reference.
• The unit parameter should be as follows: “Y”, “M”, “D”, “YM”, “YD”, or “MD”.

How to Use DATEDIF with Conditional Formatting in Google Sheets?

Using conditional formatting in Google Sheets with DATEDIF is easy. For example, say you want to track how long a task takes to complete. You can apply conditional formatting to highlight the cell when the difference with today exceeds a specific value. In the example below, I will add conditional formatting to change the cell’s color depending on the number of days that have passed.

1. 1. Type the start date into a cell.

1. 2. Type in the DATEDIF function, selecting the cell with the start date, and typing the TODAY function as the end date. For the unit, use “D”.

1. 3. Select the cell with the function and go to Format > Conditional Formatting.

1. 4. Add a rule for values lower than the limit or warning value you want to set.

1. 5. Add another rule for values equal to or above the limit or warning value.

1. 6. The cell appears green, as the value is still below the limit.

1. 7. Once another five days have passed, the cell will turn red.

Conclusion

When it comes to finding the difference between two dates, the DATEDIF function offers much more flexibility than simply subtracting the dates. First of all, you can choose the units in which you want the difference returned: days, months, or years. However, when using subtraction, the difference will always be in days, so you would need additional calculations to change the units. Additionally, you can use the special unit options to get remainders after dividing by a larger unit.

You now know all about the DATEDIF function in Google Sheets, including its syntax and the unit options available. You know how to use DATEDIF to determine the difference between dates in days, months, or years. You also know how to use the special unit options to get the remainder in days or months after dividing by years or months. Finally, you know what you need to check if the function isn’t working and how to use DATEDIF with conditional formatting.

To learn how to make a Gantt chart using DATEDIF, check out our guide on How to Make a Gantt Chart in Google Sheets.