Don’t forget to share this post

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.
DATEDIF in Google Sheets Formula Examples DATEDIF Function
DATEDIF in Google Sheets: Formula & Examples - DATEDIF Function
  1. 2. Type the start date between quotation marks followed by a comma, as shown below.
DATEDIF in Google Sheets Formula Examples Add Start Date
DATEDIF in Google Sheets: Formula & Examples - Add Start Date
  1. 3. Type the TODAY function followed by a comma.
DATEDIF in Google Sheets Formula Examples Add End Date
DATEDIF in Google Sheets: Formula & Examples - Add End Date
  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.
DATEDIF in Google Sheets Formula Examples Add Units
DATEDIF in Google Sheets: Formula & Examples - Add Units
  1. 5. Close the parenthesis and press ‘Enter’ to see the results.
DATEDIF in Google Sheets Formula Examples Difference in Days
DATEDIF in Google Sheets: Formula & Examples - Difference in Days

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.
DATEDIF in Google Sheets Formula Examples DATEDIF Function 2
DATEDIF in Google Sheets: Formula & Examples - DATEDIF Function
  1. 2. Select the cell with the start date as the first parameter and add a comma after it.
DATEDIF in Google Sheets Formula Examples Add Start Date 2
DATEDIF in Google Sheets: Formula & Examples - Add Start Date
  1. 3. Use the TODAY() function as the second parameter.
DATEDIF in Google Sheets Formula Examples Add End Date 2
DATEDIF in Google Sheets: Formula & Examples - Add End Date
  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”.
DATEDIF in Google Sheets Formula Examples Add Unit
DATEDIF in Google Sheets: Formula & Examples - Add Unit
  1. 5. Close the parenthesis and press ‘Enter’ to see the result.
DATEDIF in Google Sheets Formula Examples DATEDIF Result
DATEDIF in Google Sheets: Formula & Examples - DATEDIF Result
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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.
DATEDIF in Google Sheets Formula Examples DATEDIF Function 3
DATEDIF in Google Sheets: Formula & Examples - DATEDIF Function
  1. 2. Select the cell with the start date as the first parameter and add a comma after it.
DATEDIF in Google Sheets Formula Examples Add Start Date 3
DATEDIF in Google Sheets: Formula & Examples - Add Start Date
  1. 3. Use the TODAY function as the second parameter.
DATEDIF in Google Sheets Formula Examples Add End Date 3
DATEDIF in Google Sheets: Formula & Examples - Add End Date
  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”.
DATEDIF in Google Sheets Formula Examples Add Unit 2
DATEDIF in Google Sheets: Formula & Examples - Add Unit
  1. 5. Close the parenthesis and press ‘Enter’ to see the result.
DATEDIF in Google Sheets Formula Examples DATEDIF Result 2
DATEDIF in Google Sheets: Formula & Examples - DATEDIF 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.
DATEDIF in Google Sheets Formula Examples Add DATEDIF Function Start Date
DATEDIF in Google Sheets: Formula & Examples - Add DATEDIF Function & Start Date
  1. 2. Add the TODAY function as the second parameter.
DATEDIF in Google Sheets Formula Examples Add TODAY Function
DATEDIF in Google Sheets: Formula & Examples - Add TODAY Function
  1. 3. Add “YM” as the third parameter, close the parenthesis, and press ‘Enter’.
DATEDIF in Google Sheets Formula Examples Add YM Unit
DATEDIF in Google Sheets: Formula & Examples - Add “YM” Unit
  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.
DATEDIF in Google Sheets Formula Examples Remainder in Months
DATEDIF in Google Sheets: Formula & Examples - Remainder in 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.
DATEDIF in Google Sheets Formula Examples Remainder in Days
DATEDIF in Google Sheets: Formula & Examples - Remainder in Days
  1. 6. Finally, repeat steps 1 to 3 and use “MD” as the unit parameter. After dividing by months, the remainder is
DATEDIF in Google Sheets Formula Examples Remainder in Days 2
DATEDIF in Google Sheets: Formula & Examples - Remainder in Days

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 Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

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.
DATEDIF in Google Sheets Formula Examples Start Date
DATEDIF in Google Sheets: Formula & Examples - Start Date
  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”.
DATEDIF in Google Sheets Formula Examples DATEDIF Function 4
DATEDIF in Google Sheets: Formula & Examples - DATEDIF Function
  1. 3. Select the cell with the function and go to Format > Conditional Formatting.
DATEDIF in Google Sheets Formula Examples Format Conditional Formatting
DATEDIF in Google Sheets: Formula & Examples - Format > Conditional Formatting
  1. 4. Add a rule for values lower than the limit or warning value you want to set.
DATEDIF in Google Sheets Formula Examples Rule for Lower Values
DATEDIF in Google Sheets: Formula & Examples - Rule for Lower Values
  1. 5. Add another rule for values equal to or above the limit or warning value.
DATEDIF in Google Sheets Formula Examples Rule for Higher Values
DATEDIF in Google Sheets: Formula & Examples - Rule for Higher Values
  1. 6. The cell appears green, as the value is still below the limit.
DATEDIF in Google Sheets Formula Examples Green if Lower
DATEDIF in Google Sheets: Formula & Examples - Green if Lower
  1. 7. Once another five days have passed, the cell will turn red.
DATEDIF in Google Sheets Formula Examples Red if Higher
DATEDIF in Google Sheets: Formula & Examples - Red if Higher

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 14 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets