Mehrab Imtiaz
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
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
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.
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)
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.
To calculate the number of days between a date and the current date, we use the TODAY function:
=TODAY()-B5
We can also get the same results with the MINUS and DAYS functions that we have discussed previously:
=MINUS(TODAY(),date)=DAYS(TODAY(),date)
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)
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")
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")
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)"
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.
