Calculating the difference between two dates can get frustrating when you have to do it multiple times. 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 Google Sheets function 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.
The syntax for the DATEDIF function is as below:
=DATEDIF(date1, date2, unit) DATEDIF subtracts date2 from date1 and then returns the difference in the specified unit. The units this function supports are listed here:
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 a student's birthday, which is February 7th, 2001. Using DATEDIF you can indicate how old the student was on January 31st, 2022. Here's what each unit would return with 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 in turn 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 years are ignored, the difference between February 7th 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 a spreadsheet with the launch and touchdown dates for the Mars 2020 mission. According to this 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. You can use the DATEDIF function in Google Sheets for that. First, you need to make sure that your cells are in the correct format:
With the formatting all set, now it's time to calculate the difference using DATEDIF:
=DATEDIF(B2, B3, "d") Google Sheets will now tell you that the spacecraft has traveled for 569 days. The formula calls on DATEDIF to calculate the difference between dates B2 and B3 in days.
You can calculate how many months the spacecraft has traveled by tweaking the unit in DATEDIF:
=DATEDIF(B2,B3, "M") 
Google Sheets will now tell you that the spacecraft has traveled for 18 months. The formula here is the same as before, except that it returns the difference in months rather than days.
Let's strengthen this skill with another example. In the spreadsheet above, we have John Doe’s birthdate. The goal is to calculate how many days have passed since his last birthday, and how many days are left until his next birthday.
To achieve this, you can use the TODAY function to input today’s date in Google Sheets and then calculate the difference between today's and John’s birthdate using the DATEDIF function.
=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.
Now you have the two dates that you’re going to work with. Let’s calculate the days that have passed since John’s last birthday with DATEDIF:
=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. Once you press Enter, Google Sheets will tell you how many days it has been since John’s last birthday, which is 331 in this example at this time.
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:
=365-B3 This formula will subtract the days passed since John’s last birthday from 365, and tell you how many days are left until John’s next birthday.
Alternatively, you can also calculate the difference between two dates by converting the dates to numbers in Google Sheets. This way, you can perform mathematic functions on the dates, and subtract them the way you would subtract numbers in Google Sheets.
Get the Countdown Started: Calculate Date Differences With Google Sheets
Whether you're trying to find the number of days until an upcoming event or the number of months since a past event, Google Sheets is a great tool to help you count down the days.
With this guide, 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 and get the countdown started!
ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGifqK9dqbxur8Clmq6kkamybrDIn52eqpWjsKZ5wZ6rsJ2Vo3qlrdOeqmafn6S0rbGMrJ%2BenaSofA%3D%3D