Calculating the difference between two dates can get frustrating when you have to do it for numerous batches. Luckily, Google Sheets has a special function for this purpose. The DATEDIF function takes in the two dates and tells you how much the difference between them is in days, months, or even years.
Read on to learn what this function is, and see it in action with a simple example.
What Is the DATEDIF Function in Google Sheets?
DATEDIF is a function in Google Sheets that calculates the difference between two dates. This function can return the difference value in years, months, or days. With the proper parameters, DATEDIF can also return the months and days difference with various quirks, such as ignoring the year difference.
=DATEDIF(date1, date2, unit)
DATEDIF subtracts date2 from date1 and then returns the difference in the specified unit. The units this function supports are as below:
| Unit | Note |
|---|---|
| Y | Number of whole years between the two dates. |
| M | Number of whole months between the two dates. |
| D | Number of days between the two dates. |
| MD | Number of days between the two dates, where whole years and months are subtracted. |
| YM | Number of whole months between the two dates, where whole years are subtracted. |
| YD | Number of days between the two dates, where the two dates are considered no more than a year apart. |
Let’s use an example to better understand what each of these units means for the DATEDIF function.
As an example, we have the birthdate of a student, February 1st, 2001, and the current date is January 31st, 2022. Here’s what each unit would return in the DATEDIF function.
- Y: This will return 20, as the difference between the two dates is 20 whole years.
- M: This will return 251, as the difference between the two dates is 251 whole months.
- D: This will return 7663 since 7663 days have passed between the two dates.
- MD: This will return 24. Once the whole years and whole months are subtracted, the difference between the two dates is the day difference, which is the difference between 7 and 31 which equals 24.
- YM: This will return 11. After the whole years are subtracted, February is 11 months away from January.
- YD: This will return 358. If the two dates are only a year apart, the difference between February 1st and January 31st is 358 days.
How to Use the DATEDIF Function in Google Sheets
The DATEDIF function takes three parameters: The first date, the second date, and the unit. The dates you enter must be in the date format, and not text. The units are also limited to the ones mentioned in the previous section. With these in mind, let’s put this function to use.
As a simple example, we have the launch and touchdown dates for the Mars 2020 mission. According to this sample spreadsheet, the spacecraft took off on July 30th, 2020, and landed on Mars on February 18th, 2021.
The goal is to calculate the number of days and months that this spacecraft has traveled, using the DATEDIF function.
- Select the cells containing the dates.
- Go to Format and then Number.
- Select Date. This will set the selected cells to date format.
- Select the cell where you want to display the date difference in days. That will be cell B4 in this spreadsheet.
- Go to the formula bar and enter the formula below:
=DATEDIF(B2, B3, "d")
This formula will return the difference between dates B2 and B3 in days.
- Press Enter. Sheets will now tell you that the spacecraft has traveled for 569 days.
Now let’s calculate how many months the spacecraft has traveled.
- Select the cell where you want to display the date difference in months. That will be cell B5 in this spreadsheet.
- Enter the formula below in the formula bar:
=DATEDIF(B2,B3, "M")
This formula will return the number of months between the dates B2 and B3.
- Press Enter. Sheets will now tell you that the spacecraft has traveled for 18 months.
In another example, we have John Doe’s birthdate and want to calculate how many days have passed since his last birthday, and how many days are left until his next birthday. To achieve this, we will calculate the difference between today’s and John’s birthdate using the DATEDIF function.
- Select the cell where you have input the first date. This will be cell B1 for this example.
- Go to Format and then Numbers.
- Select Date. This will change the cell format to date.
- Select the cell where you want to show the current date. This will be cell B2 for this example.
- Go to the formula bar and enter the formula below:
=TODAY()
This formula will get today’s date and display it in the cell. The returned value is in date format, so it’s usable by other date-related functions such as DATEDIF.
- Press Enter.
Now you have the two dates that you’re going to work with. Let’s calculate the days passed since John’s last birthday.
- Select the cell where you want to show the results. We have chosen cell B3 for this example.
- Go to the formula bar and enter the formula below:
=DATEDIF(B1, B2, "YD")
This formula will calculate the difference between the dates B1 and B2. The YD parameter instructs the function to suppose that the two dates are no more than a year apart.
- Press Enter. Sheets will now tell you how many days it has been since John’s last birthday, which is 284 in this example at this time.
Now, as a side objective, let’s see how many days are left until John’s next birthday. John’s birthdays repeat every 365 days, and if 284 days have passed since his last birthday, then 365-284 days are left until his next birthday. Let’s write a formula for this:
- Select a cell where you want to show the formula results. That will be cell B4 for this example.
- Enter the formula below in the formula bar:
=365-B3
This formula will subtract the days passed since John’s last birthday from 365.
- Press Enter. Sheets will now tell you how many days are left until John’s next birthday.
Calculate the Date Difference
You now know how to use DATEDIFF to calculate the difference between two dates. You can also specify the time unit you want to see this difference in, which can be years, months, or days, along with deviations.
Time and date functions are a handy bunch in Google Sheets, and you can use them to ease your calculations. DATEDIFF isn’t the only one, though, expand your arsenal by mastering more functions.
Read Next
About The Author
