How to Add Months to a Date in Google Sheets (2 Easy Ways)

Working with dates is one of the core tasks that anyone does in a spreadsheet. And today, we will look specifically at how we can add months to a date in Google Sheets.

Let’s get started!

The General Way to Add Month(s) to a Date in Google Sheets

The most generic function that Google Sheets provides us to manipulate dates is the DATE function.

The DATE function syntax:

DATE(year, month, day)

date function syntax to add month to date in google sheets

It is almost a no-brainer of how we can use this function. Simply input the cell reference of the date we want to manipulate in each field and then add or subtract from them.

Here we see how we can add let’s say 3 months to a date using the DATE function:

=DATE(YEAR(B3),MONTH(B3)+3,DAY(B3))

adding 3 months to a date using the date function

Formula Breakdown:

  • The DATE function takes numerical values for each field since Google Sheets sees dates as numbers by default.
  • The YEAR, MONTH, and DAY functions are used to extract the numerical values of the year, month, and day respectively of the date in cell B3.
  • Since each field is a numerical value, we simply add 3 to the month field to add 3 months to the date.

Months can also be added as cell reference from another cell:

=DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3))

using date function and cell reference to add month to date in google sheets

While we have just shown how to add months using the DATE function, the function itself has much more to offer as you may have noticed. You can add or subtract years and days from a particular date with the DATE function.

But if you are specifically looking for a method or a function to only work for months, don’t worry, Google Sheets has the perfect solution that we will look at in the next section.

EDATE: The Perfect Function to Add Months to a Date in Google Sheets

EDATE is the perfect function to use when looking to manipulate the month section of any date in Google Sheets.

The EDATE function syntax:

EDATE(start_date, months)

edate function syntax

As you may have already guessed, the start_date section takes the date that will be changed. The months field takes the number of months we want the date to change by; negative integer for months before and positive for months after.

Thus, our formula to add 3 months to date with the EDATE function is:

=EDATE(B3,3)

using edate function to add months to date in google sheets

Working with different dates and different addition conditions for months using cell reference:

=EDATE(B3,C3)

using cell reference for months field for the edate function

And that is it! It is just that easy to manipulate months from a date with the EDATE function.

Tips: Date Format

The date format we have used is MM/DD/YYYY (Month/Day/Year), but depending on your locale it can also be DD/MM/YYYY (Day/Month/Year).

If you want to avoid such mismatches, you can update what format your Google Sheets uses from the Custom date and time option. To navigate to it, you have to go through the Format tab and then Number.

Format > Number >Custom date and time

navigating to custom date and time option from the format tab

From the Custom date and time window, you can always adopt one of the pre-existing formats or create one of your own to suit your needs.

all formats present in the custom date and time window

Final Words

That concludes all the ways we can add months to a date in Google Sheets. While the DATE function is more versatile when working with dates, the EDATE has proven to be quite efficient when it comes to only working with the months of a complete date.

We hope that the methods we have discussed come in handy for your day-to-day tasks. 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