Adding months to dates could be very useful.
For example, you have the manufacturing date of multiple products with the number of months they are viable.
You can calculate the expiry date of the product by adding the number of months from the manufacturing date.
This can be done quickly, using MS Excel.
4 Methods to add months to a date using Microsoft Excel:
- Use the EDATE function
- Use the MONTH function
- Use Microsoft VBA code
- Use the Fill Series option
Let us begin by looking at each of these methods in detail.
Method #1: Use the EDATE Function
This method is the simplest way to add months to a date. You simply give a date and the number of months to be added as parameters. The function will add the months to the given date and output the calculated date.
Step #1: Open your MS Excel workbook
If you want to create a new workbook, go to your MS Excel application.
Click on Blank workbook to create a new workbook.
After you create the workbook, enter the required data. If you already have a workbook with the required data, you can directly open that workbook.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to A2.
Step #3: Enter the formula
The Excel formula is =EDATE(Cell name, months)
This means that you will start typing the formula into the cell, but replace Cell name with the cell that contains the manufacturing date in your spreadsheet.
In our example, it is cell A2 that contains this date.
In your formula, replace months with the cell containing the number of months to be added. In our example, that is B2.
Step #4: Press Enter
Now press Enter.
You will see the calculated date in the Expiry Date column.
Method #2: Using the MONTH function
In this method, you use the MONTH function to add the months. This formula can be used in your spreadsheets when you have to generate and manipulate data for reporting purposes.
Step #1: Open your MS Excel workbook
If you want to create a new workbook, go to your MS Excel application.
Click on Blank workbook to create a new workbook.
After you create the workbook, enter the required data. If you already have a workbook with the required data, you can directly open that workbook.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to A2.
Step #3: Enter the formula
The Excel formula is =DATE(YEAR(date), MONTH(date)+month, DAY(date))
This means that you will start typing the formula into the cell, but replace date with the cell that contains the manufacturing date in your spreadsheet. In our example, it is cell A3 that contains this date.
In your formula, replace month with the cell containing the number of months to be added. In our example, that is B3.
Our example formula will look like this:
=DATE(YEAR(A3), MONTH(A3)+B3, DAY(A3))
Step #4: Press Enter
After typing in the formula, press Enter.
Now, you’ll be able to see that the date and the month have been added in the Expiry Date column.
Method #3: Using Microsoft VBA Code
This is a method that uses Microsoft’s Visual Basic application to get the age. It is a very useful method to know and apply to your spreadsheets if you work with dates and calculations every single day.
Step #1: Open your MS Excel workbook
If you want to create a new workbook, go to your MS Excel application.
Click on Blank workbook to create a new workbook.
After you create the workbook, enter the required data. If you already have a workbook with the required data, you can directly open that workbook.
Step #2: Open Microsoft Visual Basic
Go to the Developer tab and click on the Visual Basic icon to the extreme left.
Step #3: Insert a new module
Once the Microsoft Visual Basic for Applications dialog box is open, click on Insert.
Now, select Module from the drop-down options that appear.
Step #4: Copy and paste the code
This code takes in the date of birth as a parameter and gives the number of completed years.
Copy the below code and paste it into the Module 1 dialog box.
Function add_months(Date1 As Date, Months As Integer) As Double
add_months = DateAdd("m", Months, Date1)
End Function
Once done, click on the X in the corner to close the window, and exit the Visual Basic window as well.
Step #5: Use the module function
Click on a cell where you want the date to appear.
Enter =add_months(date, months) into the cell.
Replace date and month with the cells containing your date and the number of months to be added.
In our example, that is A4 for the manufacturing date, and B4 for the number of months to be added.
Our example function will look like this:
=add_months(A4, B4)
Press Enter.
You will now see the new expiry date.
Method #4: Use the Fill Series Option
You can use this method if you have a fixed number of months to be added. It is a great way to very quickly, with only a few clicks, get the calculated date that you need.
Step #1: Create or open your MS Excel workbook
If you want to create a new workbook, go to your MS Excel application.
Click on Blank workbook to create a new workbook.
After you create the workbook, enter the required data. If you already have a workbook with the required data, you can directly open that workbook.
Step #2: Select the cells
You need to select two cells: the cell with the old date (manufacturing date) and the cell with the new date (expiry date).
To do that, click with your mouse on the manufacturing date and then drag your mouse over the empty Expiry Date field next to it, before releasing the mouse button.
Step #3: Fill and select Series
Go to the Home tab.
On the right of the Home tab ribbon, you will see a Fill icon with a small dropdown arrow.
Click on it and select Series from the drop-down.
Step #4: Enter the number of months to be added
In the Series window that has now opened, check that Rows in the ‘Series in’ section have been selected, as well as Date in the ‘Type’ section.
Click on the option Month in the ‘Date unit’ section.
Enter the number of months in the Step value field.
Once everything is complete, click on the OK button.
You will now see that the months have been added to the Expiry Date.
Conclusion
There are a great many ways to add months to a date in a spreadsheet, and they are all important for various business purposes. It is good to remember the different options and apply these methods as you need them.