In previous article, I mentioned How To Reset Excel Settings but in this article, I have mentioned How To Add Months To Date In Google Sheets using various possible methods.
You may need to add months to date when using Google Sheets without changing the day or year. Learning how to do this is important, especially when you are dealing with a large series of dates. It also helps in fixing future deadlines or appointments.
There are four methods you can use to add months to date in Google Sheets, and these methods will be discussed in this article.
Method One: Add Months To Date Using The DATE Function
This method is the quickest way to add months to date on Google Sheets. It is helpful when you have several dates in arrays and the months have to be fixed. In the example, the next appointment with clients occurs after two months.
Follow these steps to use the DATE function:
- Open the spreadsheet you want to edit using Google Sheets.
- Select the column you wish to edit. In this example, let’s say column A here.
- Click on Format in the menu bar.
- In the drop-down menu, hover your mouse over the Number option.
- Click on Custom number format. Type d/m/yyyy in the text field, and click on Apply.
- Double-click on the cell adjacent to your date field and type the following formula:
=DATE(YEAR(Cell Number),MONTH(Cell Number)+z,DAY(Cell Number))
- Replace the Cell Number with the cell that contains the date. In this example, the cell is A2.
- Replace z with the number of months you want to add to the first date. For instance, let’s add 2 months to the first appointment date in this illustration. Therefore, z becomes 2, and the required formula is now:
=DATE(YEAR(A2), MONTH(A2)+2,DAY(A2))
- Press Enter to get the result for the first entry.
- You may get an error message if the day in this cell has two digits. That is, 10 and upward.
- To fix this, enter a digit number as the day. Next, double-click on the cell and select the desired date from the calendar that pops up.
- After the first entry has been calculated, click on the cell again to select it. You will see a blue selection frame around it.
- Place the cursor at the bottom left corner of the selection box, and it will turn into a Plus icon.
- Click and hold the left-click button, then drag the cursor to cover the last cell you want to calculate. In the example, the last cell is B7.
- Release the left-click button.
After you release the button, dates for column B will be calculated.
Method Two: Add Months To Date Using The EDATE Function
Follow these steps to add months to date using the EDATE function:
- Open the spreadsheet you want to edit using Google Sheets.
- Select the column you wish to edit. In this example, we select column A to edit it.
- Click on Format in the menu bar.
- In the drop-down menu, hover your mouse over the Number option.
- Click on Custom number format. Use the d/m/yyyy date format, and click on Apply.
- Double-click on the cell adjacent to your date field and paste the following formula:
=EDATE(Cell Number One, Cell Number Two)
- Cell Number One is the cell that contains your date. In this example, it is cell A2.
- Cell Number Two is the cell containing the number of months to be added.
The formula for the example is:
=EDATE(A2,B2)
- Press the Enter key to calculate the result of the first entry.
- The system may ask to autofill other cells after the result has been calculated, click on the marker icon in the autofill prompt to do this.
- Otherwise, Click the cell containing the result, and you will see the blue rectangular selection bubble around it.
- Hover your cursor over the bottom right corner of the selection bubble till you see a plus icon.
- Hold the left-click button and drag the cursor to cover the last cell in the column, C7 in this case.
- Release the left-click button.
After you have completed these steps, the final dates will appear in column C.
NOTE: Some cells may return an error value if the day in the cell is two digits, for example, as in cell A7. Change the day to one digit, then double-click the cell and select the desired two-digit day from the calendar that pops up.
Method Three: Add Months To Date Using The End Of Month Function
This function is proper when you want to set the date to the end of the month or if you want to set it to the end before adding a few months and days. Follow these steps to add months to date using the End Of Month function:
- Open the spreadsheet using Google Sheets.
- Select the column you wish to edit. In this example, we wish to edit column A, so capital A is clicked
- Click on Format in the menu bar.
- In the drop-down menu, hover your mouse over the Number option.
- Click on Custom number format. Use the d/m/yyyy date format, and click on Apply.
- Double-click on the cell adjacent to your date field and type the following formula:
=EOMONTH(Cell Number,y)+x
- Cell Number represents the cell that contains the date. In the example, this cell is A2.
- y represents the number of months to be added.
- x represents the number of days to be added after the end of the month. No days are added to the end of the month in this example.
In this example, two months are to be added. The formula becomes:
=EOMONTH(A2,2)
- Press the Enter key to get the result of the first entry.
- After the result has been calculated, place your cursor on the cell containing the result.
- Click on the cell and hold the left-click button of the mouse. Drag the cursor to the cell next to the last date.
- Release the left-click button and new dates will appear in the appropriate cells.
Conclusion
That is all on how to add months to date in Google Sheets.
You may also like to read:
If Contains Google Sheets Formula (With Examples)
how to export google calendar to google sheets
Export Google Calendar To Excel
How to Add Background Image to Google Calendar Event
How To Use Fraction (Not "/") In Google Docs