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. Show 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 subtracts date2 from date1 and then returns the difference in the specified unit. The units this function supports are listed here:
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.
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.
Now let's calculate how many months the spacecraft has traveled.
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 use the TODAY function to input today’s date in Google Sheets and then calculate the difference between today and John’s birthdate using the DATEDIF function.
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.
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:
Sheets will now 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. Calculate the Date DifferenceYou 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. |