How to Calculate the Difference Between Two Dates in Google Sheets

Rate this post
How to Calculate the Difference Between Two Dates in Google Sheets

When you have to calculate the difference between two dates many times, it might get tedious. Fortunately, Google Sheets includes a capability for this.

The DATEDIF function accepts two dates and returns the difference between them in days, months, or even years. Continue reading to learn about this function and see it in action with a simple example.

What Is the DATEDIF Function in Google Sheets?

The DATEDIF function in Google Sheets computes the difference between two dates. The difference value may be returned in years, months, or days using this method. DATEDIF may also return the months and days difference with additional peculiarities, such as omitting the year difference, given the correct options.

The syntax for the DATEDIF function is as below:

=DATEDIF(date1, date2, unit)

Date2 is subtracted from date1 and the difference is returned in the chosen unit. The following units are supported by this function:

UnitNote
YNumber of whole years between the two dates.
MNumber of whole months between the two dates.
DNumber of days between the two dates.
MDNumber of days between the two dates, where whole years and months are subtracted.
YMNumber of whole months between the two dates, where whole years are subtracted.
YDNumber of days between the two dates, where the two dates are considered no more than a year apart.

Let’s look at an example to see what each of these units represents for the DATEDIF function.

As an example, a student was born on February 1st, 2001, and the current date is January 31st, 2022. In the DATEDIF function, here’s what each unit would yield.

  • Y: Because the gap between the two dates is 20 years, this will return 20.
  • M: Because the distance between the two dates is 251 months, this will return 251.
  • D: Because 7663 days have transpired between the two dates, this will yield 7663.
  • MD: This will get a result of 24. After subtracting the whole years and months, the difference between the two dates is the day difference, which is the difference between 7 and 31, which equals 24.
  • YM: This will yield 11. After subtracting the whole years, February is 11 months apart from January.
  • YD: This will yield 358. The gap between February 1st and January 31st is 358 days if the two dates are merely a year apart.
  8 Google Docs Add-Ons to Improve Your Citation and Bibliography

How to Use the DATEDIF Function in Google Sheets

The initial date, the second date, and the unit are all sent to the DATEDIF function. Dates must be entered in date format rather than text. The units are also restricted to those listed in the preceding section. With these considerations in mind, let us put this function to use.

As an example, we have the Mars 2020 mission’s launch and landing dates. The spacecraft is scheduled to launch on July 30, 2020, and settle on Mars on February 18, 2021, according to this sample spreadsheet.

The purpose is to use the DATEDIF function to compute the number of days and months that this spaceship has gone.

  1. Choose the cells that contain the dates.
  2. Select Format, then Number.
  3. Date should be chosen. This will convert the cells you’ve chosen to date format.
  4. Choose the cell where you want the date difference in days shown. In this spreadsheet, it will be cell B4.
  5. Enter the following formula in the formula bar: =DATEDIF(B2, B3, “d”) (B2, B3, “d”)This formula will yield the difference in days between dates B2 and B3.
  6. Enter your password. Sheets will now inform you that the spacecraft has been in orbit for 569 days.

Let us now compute how many months the spaceship has gone.

  1. Choose the cell where you wish the date difference in months to be shown. In this spreadsheet, it will be cell B5.
  2. In the formula bar, enter the following formula:=DATEDIF (B2,B3, “M”)The number of months between the dates B2 and B3 will be returned using this formula.
  3. Enter your password. Sheets will now inform you that the spacecraft has been in orbit for 18 months.
  5 Free Sites and Extensions to Make Google Maps Incredibly Useful

In another case, we have John Doe’s birthdate and want to know how many days have elapsed since his last birthday and how many days remain till his next birthday.

To do this, we will use the TODAY function to enter today’s date into Google Sheets and then use the DATEDIF function to determine the difference between today and John’s birthday.

  1. Choose the cell where you entered the initial date. For this example, this will be cell B1.
  2. Select Format, then Numbers.
  3. Date should be chosen. The cell format will be changed to date.
  4. Choose the cell in which you wish to display the current date. For our example, this will be cell B2.
  5. Enter the following formula in the formula bar: =TODAY() This formula will retrieve the current date and show it in the cell. Because the returned result is in date format, it may be used by other date-related methods like DATEDIF.
  6. Press Enter.

You now have the two dates on which you will work. Let’s see how many days have elapsed since John’s last birthday.

  1. Choose the cell where you wish the results to appear. For this example, we’ve picked cell B3.
  2. Enter the following formula in the formula bar: B1, B2, “YD”) =DATEDIF(B1, B2) This formula will compute the difference between B1 and B2. The YD option informs the function to assume that the two dates are within a year of each other.
  3. Enter your password. Sheets will now tell you how many days have passed since John’s last birthday, which is 284 in this case.
  How to Count Non-Blank Cells in Google Sheets

Let’s see how many days it is before John’s next birthday as a side goal. John’s birthdays occur every 365 days, thus if it has been 284 days since his previous birthday, it will be 365-284 days before his next birthday. Let’s make a formula for it:

  1. Choose a cell to display the formula results in. For this case, it will be cell B4.
  2. In the formula bar, enter the following formula:=365-B3 This formula subtracts the number of days since John’s last birthday from 365.
  3. Press Enter.

Sheets will now display the number of days till John’s next birthday. You may also determine the difference between two dates in Google Sheets by converting the dates to numbers. This manner, you can run arithmetic operations on the dates and subtract them in Google Sheets just as you would numbers.

Calculate the Date Difference

You now understand how to use DATEDIFF to get the difference between two dates. You can also specify the time unit in which you want to see this difference, which can be years, months, or days, as well as the 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.

You are looking for information, articles, knowledge about the topic How to Calculate the Difference Between Two Dates in Google Sheets on internet, you do not find the information you need! Here are the best content compiled and compiled by the achindutemple.org team, along with other related topics such as: Google.

Similar Posts