How to Subtract Months from Date in Google Sheets (3 Easy Ways)

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)

date function syntax - subtract months in google sheets

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.

dataset to subtract months from in google sheets

Our formula:

=DATE(YEAR(B3),MONTH(B3)-5,DAY(B3))

subtract months from date in google sheets using the date function

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))

subtract months from date in google sheets using the date function animated

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)

edate function syntax

Let’s directly jump into the action:

=EDATE(B3,-5)

subtract months from date in google sheets using edate function

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)

updating edate formula with cell reference

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)

datedif function syntax

For our example, let’s use the following dataset to find the difference between dates in months in Google Sheets:

dataset to find the difference in months

Our formula:

=DATEDIF(B3,C3,"M")

finding the difference in months between dates in google sheets using the datedif function

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")

difference in months from the current date to a date

To calculate the number of months from a date in the past till the current date:

=DATEDIF(B3,TODAY(),"M")

difference in months from a date till current date

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

employee dataset to calculate tenure

However, that is the end of anything complicated for this example. Because the general formula remains unchanged:

=DATEDIF(C3,D3,"M")

calculating tenure in months in google sheets

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.

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