Md. Abdullah Al Murad
For project planning or time management, we need to make a routine or schedule. Making time schedules, MS Excel provides different ways to add months to date. In this article, I am going to show how to add months to date using Excel Functions and formulas.
Download the Practice Workbook
5 Suitable Ways to Add Months to Date in Excel
First, get introduced to our dataset that represents some orders. The attributes are Order ID, Order Date, Process Time (Months), and Delivery Date. Our task will be to automatically calculate delivery dates by adding the processing time to the order date.
1. Using EDATE Function to Add Months to Date in Excel
In this part, we will use Excel’s popular function which is the EDATE function. Before going to the main example let’s see the fundamentals of this function.
In this function, the first argument takes the starting date or the given date which will be updated. Then the number of months that will be added or subtracted from the starting date.
Steps:
- Enter the formula in Cell E5.
=EDATE(C5, D5)
Formula Explanation:
Here in this formula, all our Order Dates are in the C column and Processing times are in the D column. That’s why I have passed C5 cell as my starting date and D5 as my month’s number.
[Note: Make sure that your D column cells are in Date format]
- Copy down the formula by using the Fill Handle tool up to Cell E12.
Then you will get the output like the image below.
Read More: How to Add 6 Months to a Date in Excel (2 Easy Ways)
2. Using MONTH & Date Functions to Add Month to Date in ExcelÂ
Now let’s add months to date using the MONTH and DATE functions. Let’s see their syntax first.
This function returns the month of a date denoted by a serial number. In its parameter, the serial number is a must. The date of the month we are attempting to find and dates should be inserted by using the MONTH function.
DATE (year, month, day)This Excel function is used when we need to take three separate values and merge them to form a date. We need to pass year, month, and day values to form a full date using the DATE function.
Now for showing this method we will consider the same example above.
Steps:
- Enter the formula in Cell E5.
=DATE(YEAR(C5),MONTH(C5)+D5,DAY(C5))
Formula Explanation:
As I have mentioned earlier DATE function takes three arguments in its parameter. For this example we only need to increase the month, that’s why in the second argument I have passed MONTH(C5)+D5  to increase the month by a specific number. Others will be the same as it was.
- Later, copy down the formula by using the Fill Handle tool up to Cell E12.
Look, we got the same output as the first method.
[Note: Make sure that your D column cells are in Date format]
Read More: Excel Formula to Calculate Number of Days Between Today and Another Date
3. Combine COUNTIFS and EDATE Functions to Add Months to Date
Let’s say that we have a data set of orders. The attributes are Order ID and Delivery Date. Now we will consider the date and count how many orders will be delivered from the specific date to the next 4 months. For this example, let’s consider our time duration is June 2020 to September 2020. Here, we’ll use the COUNTIFS and EDATE functions to perform the task.
Steps:
- Enter the formula in Cell F6 and press Enter.
=COUNTIFS(C5:C12,">="&F4,C5:C12,"<"&EDATE(F4,4))
Formula Explanation:
Here additionally I have used the COUNTIFS function with EDATE. Let’s see the COUNTIFS function first.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
This is almost the same as the COUNTIF function but the only basic difference is that it can take multiple ranges and conditions at a time in its parameter as an argument. In summary, it can be an alternative to multiple COUNTIFS functions.
Using the COUNTIFS function we are counting the cells with this range C5:C12 Â and our condition is from the date in cell F4 to the next 4 months which is calculated by the EDATE function.
Read More: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)
Similar Readings
- How to Count Months from Date to Today by Using Excel Formula
- Excel Formula to Find Date or Days for Next Month (6 Quick Ways)
- How to Calculate 90 Days from a Specific Date in Excel
- Calculate Overdue Days in Excel (4 Easy Ways)
- How to Add 3 Years to a Date in Excel (3 Effective Ways)
4. Using Fill Series Option to Add 1 Month to Date in Excel
Let’s do the same thing using the same example above but here we will increase the month by 4 for every order. So, our task will be to increase the Oder date by 4 and show them in the Delivery Date column. Our starting date is 1/2/2020. Now all the Order dates for each order will be given by increasing the current date by 4 sequentially.
Steps:
- Select Cell C4 and select the cells up to C12.
- Go to the Home tab.
- Under the Editing section, select the Fill option.
- Next, select the Series option.
- Select Columns in Series.
- Then select Date as Type.
- Date Unit will be Month.
- Step value will be 1.
- Then press the OK button.
Soon after, all the dates will be entered automatically.
Read More: How to Add 3 Months to a Date in Excel (4 Easy Methods)
5. Run a VBA Code to Add Months to Date
In our last method, we’ll show how to use VBA for adding months. Excel VBA has a function named DateAdd, we’ll use it here. Also, it will have input boxes, so that we can insert the date and number of months. The VBA macro will add 3 months with the date 5/2/202.
Steps:
- Firstly, press Alt + F11 to open the VBA window.
- Then, click Insert > Module to create a new module.
- Next, type the following codes in the module-
Sub AddMonths()
Dim StartDate As Date
Dim AddingMonths As String
Dim mNumber As Integer
Dim Message As String
AddingMonths = "m"
StartDate = InputBox("Insert the Starting Date")
mNumber = InputBox("Insert Number of Adding Months")
Message = "Updated Date: " & DateAdd(AddingMonths, mNumber, StartDate)
MsgBox Message
End Sub- Then, press the Run icon to run the codes.
- After appearing the Macros dialog box, select the macro name and press Run.
- The first input box will allow you to insert the date, insert the date and press OK or Enter.
- After that, another input box will allow you to insert the number of months. Type the number and press OK or Enter.
A notification box will show the output.
Read More: Calculate Number of Days between Two Dates with VBA in Excel
Conclusion
These are the ways to add months to date in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.
Related Articles
- How to Add 7 Days to a Date in Excel (3 Methods)
- Excel Formula for Number of Days Between Two Dates
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Calculate Age on a Specific Date with Formula in Excel
- How to Add Years to a Date in Excel (3 Easy Ways)
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
