How to Subtract Dates in Google Sheets (An Easy Guide)

Dates in Google Sheets are seen as whole numbers, with each day representing one unit. This makes calculations, like how to subtract dates in Google Sheets, that much easier.

How to Subtract Dates in Google Sheets

The simplest way to subtract with dates is to use arithmetic subtraction or minus (-).

Since every date is held as an integer, subtracting a number of days from a date looks something like this:

=B3-5

using minus to subtract date in google sheets

With “-5” we have subtracted 5 days from the date in cell B3 to get a date 5 days earlier.

The rest of the articles show a few different methods of how to subtract dates in Google Sheets following the same fundamental approach of arithmetic minus.

1. Subtract Dates to Find the Difference in Days in Google Sheets

One of the more common uses of subtracting dates comes when calculating the number of days between two dates in Google Sheets. To do that, we simply minus the later date with the previous date.

=C3-B3

subtract dates in google sheets to find the number of days

Since each integer value represents a day in Google Sheets, the difference we get is the number of days between the two dates.

But that doesn’t end there. Depending on the situation, Google Sheets provides us with a few functions that we can use to achieve the same results.

The MINUS Function

The MINUS function is a great way to present differences between two values in Google Sheets. This can easily be applied to date calculations to find the number of days between two dates.

=MINUS(end_date,start_date)

using the minus function to subtract dates in google sheets

The DAYS Function

Alternatively, the DAYS function provides the perfect alternative, and perhaps the best way, to find the difference in days between two dates.

=DAYS(C3,B3)

using the days function to find the number of days in google sheets

The two functions above are some of the simplest ways to calculate the number of days between two dates in Google Sheets, and both use the subtraction approach.

Calculate Days Between Date Today

The best way to generate the current date is by using the TODAY function of Google Sheets. This is a standalone function, taking no parameters. However, TODAY generates a “running” date that updates every time the worksheet is refreshed.

generating the current date with the today function

To calculate the number of days between a date and the current date, we use the TODAY function:

=TODAY()-B5

subtract date from today in google sheets

We can also get the same results with the MINUS and DAYS functions that we have discussed previously:

=MINUS(TODAY(),date)
=DAYS(TODAY(),date)

using minus and days function to find the number of days from today in google sheets

Learn More: Count Days From Today in Google Sheets (5 Easy Ways)

2. Using DATEDIF Function to Subtract Dates in Google Sheets

Perhaps the best way to subtract dates in Google Sheets is by using the DATEDIF function.

DATEDIF(start_date, end_date, unit)

datedif function syntax

The sole purpose of this function is to find the difference between the two dates in terms of the unit provided.

The type/unit of result a user can extract:

  • “Y”: Gives the total number of whole years between the start date and end date.
  • “M”: Gives the total number of whole months between the start date and end date.
  • “D”: Gives the total number of days between the start date and end date.
  • “MD”: Gives the number of days between the start date and end date without counting the months or years completed.
  • “YM”: Gives the number of whole months between the start date and end date without counting the months and years completed.
  • “YD”: Gives the number of days between the start date and end date, assuming that the date range is no more than one year apart.

So, to subtract dates in Google Sheets to find the number of days between two dates, our DATEDIF formula is:

=DATEDIF(B3,C3,"D")

subtract dates in google sheets with the datedif function

As you can see, the results are the same as the approaches we took in the previous section. However, just finding only the difference in days is a very inefficient use of the DATEDIF function. Thus, it provides so many units for us to use in the same function.

Subtract Dates in Terms of Months

Now, let’s do the calculation in terms of months. We simply update our formula to:

=DATEDIF(B3,C3,"M")

calculate difference between dates in terms of months in google sheets

The “M” is the unit for months. Note that the months calculated are complete months according to the calendar, the days are not just divided by 30.

Calculate Tenure in Google Sheets

The best practical use of subtracting dates in Google Sheets is to find the tenure of employees. Thanks to the function’s versatility of the type of outputs it can give, we can intuitively present our results.

Most tenure calculations are presented in terms of years and months. So, we have to get slightly creative with our formula:

=DATEDIF(C3,D3,"Y")&" Year(s) and "&DATEDIF(C3,D3,"YM")&" Month(s)"

finding tenure in google sheets using datedif function

We have concatenated the results that we got by subtracting the start and end dates in years (“Y”) and in months after years (“YM”). As a user, you can similarly combine the different outputs as your worksheets or organization requires.

Learn More: How to Calculate Tenure in Google Sheets (An Easy Guide)

Final Words

That concludes all the ways we can subtract dates in Google Sheets. While the simple arithmetic minus may suffice in most cases when calculating the number of days, using the DAYS function is recommended for a professional outlook. Other functions, like DATEDIF, can take advantage of the different date values when subtracting between two dates.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo