Mehrab Imtiaz
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)
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))
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))
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)
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)
Working with different dates and different addition conditions for months using cell reference:
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
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.
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.
