Mehrab Imtiaz
In today’s tutorial, we will look at how we can subtract months from a date in Google Sheets, and another month calculation that is commonly used in a practical setting.
Let’s get started.
3 Ways to Subtract Months from Date in Google Sheets
1. Using the DATE function to Subtract Months in Google Sheets
The DATE function is the go-to approach for many users when it comes to adding or subtracting date values in Google Sheets.
DATE(year, month, day)
As you can see, the function takes all three values: year, month, and day, into the equation to calculate. However, this time, we will only focus on the month side of things.
Here we have a simple dataset of dates (MM/DD/YYYY) from which we will be subtracting 5 months. Let’s see how we can do that with the DATE function.
Our formula:
=DATE(YEAR(B3),MONTH(B3)-5,DAY(B3))
As you can see, the DATE function needs you to extract the individual fields of the year, month, and day from the date with the YEAR, MONTH, and DAY functions. Only then can you add or subtract from these fields.
To subtract 5 months from the date, we simply minus (-) from the month field of the function.
Note: To add to any field, just use plus (+).
We can even use a cell reference to slightly automate our task:
=DATE(YEAR(B3),MONTH(B3)-$E$3,DAY(B3))
Note: We have locked our cell reference with absolutes ($) for all result cells.
2. Using EDATE Function to Subtract Months in Google Sheets
One of the biggest problems with the DATE function is that no matter which field you are calculating, you have to extract all three date fields every time. So, you can see why we consider it to be inefficient when we are only calculating with the month field.
Fortunately for us, we have a perfect alternative for DATE when it comes to adding or subtracting only months from a date. It is the EDATE function.
The EDATE function syntax:
EDATE(start_date, months)
Let’s directly jump into the action:
To subtract months from date using the EDATE function, we must put a negative number in the [months] field. Inputting a positive number will add the months.
As simple as this is, we have to get slightly creative if we want to extract the number of months to subtract from a different cell. Especially if it is given as a positive integer as we have in our worksheet.
In such cases, we update our formula to something like this:
=EDATE(B3,"-"&$E$3)
The minus (-) and the subtraction value has to be concatenated with an ampersand (&) to get the job done.
3. Calculate the Difference between two Dates in Terms of Month
One of the more common date calculations we do in a spreadsheet is to find the difference between two days. Thus following the subject of our article today, we will see how we can find the difference in months between the two dates.
The perfect function for this method is the DATEDIF function.
DATEDIF(start_date, end_date, unit)
For our example, let’s use the following dataset to find the difference between dates in months in Google Sheets:
Our formula:
=DATEDIF(B3,C3,"M")
The “M” in the unit field stands for months, which is used to find the difference in months. Similarly, we can use “D” for days and “Y” for years.
Calculate Months from Today
We can pair the DATEDIF function with the TODAY function to find the difference in months between a date and the current date.
To calculate the number of months from the current date to a date in the future:
=DATEDIF(TODAY(),B3,"M")
To calculate the number of months from a date in the past till the current date:
=DATEDIF(B3,TODAY(),"M")
Example: Calculate Tenure in Months in Google Sheets
Let’s use whatever we have learned so far to use it in something practical: Calculating tenure in terms of months.
In a practical setting, there will be a lot of entries where the employees are still employed. For those end dates, we use the TODAY function as it gives a “running” date that updates with the worksheet automatically
However, that is the end of anything complicated for this example. Because the general formula remains unchanged:
=DATEDIF(C3,D3,"M")
Learn More: How to Calculate Tenure in Google Sheets (An Easy Guide)
Final Words
That concludes all the ways we can subtract months from a date in Google Sheets. Thanks to functions like EDATE and DATEDIF that can focus on only month calculation, the task becomes that much easier.
Feel free to leave any queries or advice you might have for us in the comments section below.
