4 Methods to add months to a date in Google Sheets:
- Use the DATE function when the number of months to be added is the same.
- Use the EDATE function when the number of months to be added are different.
- Use the End of Month function.
- Use a generic formula (handy only in particular cases).
Let us begin.
Method #1: Use the DATE Function
This is the quickest way to add months to a given date. It is helpful if there is an array with a date field and we wish to add a fixed number of months to a date. An example would be food that expires 3 months after manufacturing.
Step #1: Open the Google sheet
Open the Google sheet in which you have the date field and where you want to add months.
Step #2: Format the number type
Select the desired column (in our case, column A) by clicking on the capital A.
Click on Format in the main menu bar.
In the drop down menu that opens, hover your mouse over the option Number.
From the submenu that opens, click on Custom number format.
In our example, we have used the d/m/yyyy format.
Once you have entered the format as d/m/yyyy, click on the Apply button.
Step #3: Enter the formula
Double click on the adjacent cell to your date field, and type this formula:
=DATE(YEAR(Cell Number),MONTH(Cell Number)+z,DAY(Cell Number))- Cell Number is the cell in which your date is. In our case, it is A2.
- ‘z’ denotes the number of months to be added.
For example: The delivery date of a particular product is 5 months after the date of sales.
The date of sale is recorded in Column ‘A’. So, the required formula in the adjacent cell will be
=DATE(YEAR(A2),MONTH(A2)+5,DAY(A2))
Step #4: Press enter to get the result
Press enter to get the result for the first entry in the Google sheet.
Once the first field has been calculated, place your cursor on the field with the formula. In our example, that would be B2.
A blue frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be B6.
Release the mouse button.
New delivery dates will show in column B.
Method #2: Use the EDATE Function
This is also an easy way to calculate the month of choice without any hassle.
Step #1: Open the Google sheet
Open the Google sheet in which you have the date field and where you want to add months.
Step #2: Format the number type
Click and Select the desired column (in our case, column A)
Click on Format in the main menu bar.
In the drop down menu that opens, hover your mouse over the option Number.
From the submenu that opens, click on Custom number format.
In our example, we have used the d/m/yyyy format.
Once you have entered the format as d/m/yyyy, click on the Apply button.
Step #3: Enter the formula
Double-click on the adjacent cell to your date field, and type this formula:
=EDATE(First Cell Number, Second Cell Number)- First Cell Number is the cell in which your date is. In our case, it is A2.
- Second Cell Number is the cell containing the number of months to be added.
For our example, the required formula in the adjacent cell will be:
=EDATE(A2,B2)
Step #4: Press enter to get the result
Press enter to get the result for the first entry in the Google sheet.
Once the first field has been calculated, place your cursor on the field with the formula. In our example, that would be C2.
A blue frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be C10.
Release the mouse button.
New final dates will show in column C.
Method #3: Use the End of Month Function
This is useful when you need to set the date to the end of the month or if you want to set a date at the end of the month and add a few months plus a few days.
Step #1: Open the Google sheet
Open the Google sheet in which you have the date field and where you want to add months.
Step #2: Format the number type
Select the desired column (in our case, column A) by clicking in the capital A.
Click on Format in the main menu bar.
In the drop down menu that opens, hover your mouse over the option Number.
From the submenu that opens, click on Custom number format.
In our example, we have used the d/m/yyyy format.
Once you have entered the format as d/m/yyyy, click on the Apply button.
Step #3: Enter the formula
Double click on the adjacent cell to your date field, and type this formula:
=EOMONTH(Cell Number,y)+x- Cell Number is the cell in which your date is. In our case, it is A2.
- y indicates the number of months to be added.
- x indicates the number of days to add after month-end.
In our example, the x is zero, so our formula will be:
=EOMONTH(A2,3)If you needed to get to 3 months and 10 days after the end of month 3, the formula would be:
=EOMONTH(A2,3)+10
Step #4: Press enter to get the result
Press enter to get the result for the first entry in the Google sheet.
Once the first field has been calculated, place your cursor on the field with the formula. In our example, that would be B2.
A blue frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be B10.
Release the mouse button.
New final dates will show in column B.
Method #4: Use a Generic Formula
This method is useful when you want to add a fixed time frame, say six months or one quarter of a year.
Step #1: Open the Google sheet
Open the Google sheet in which you have the date field and where you want to add months.
Step #2: Format the number type
Select the desired column (in our case, column A) by clicking in the capital A.
Click on Format in the main menu bar.
In the drop down menu that opens, hover your mouse over the option Number.
From the submenu that opens, click on Custom number format.
In our example, we have used the d/m/yyyy format.
Once you have entered the format as d/m/yyyy, click on the Apply button.
Step #3: Enter the formula
Double click on the adjacent cell to your date field, and type this formula:
=Cell Number+(z*365)- Cell Number is the cell in which your date is. In our case, it is A2.
- z represents the months to be added as a fraction of a year.
For example, if you wish to add 6 months to a date, then the formula will be
=A2+(0.5*365).
Step #4: Press enter to get the result
Press enter to get the result for the first entry in the Google sheet.
Once the first field has been calculated, place your cursor on the field with the formula. In our example, that would be B2.
A blue frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be B10.
Release the mouse button.
New final dates will show in column B.
Conclusion
Scheduling tasks can be easily done by using any of the above methods. Be sure about what you need to add – a fraction of a year, variable months, or a fixed number of months – to decide which method to use.
this was very helpful!!