What Is Excel Add Months To Date?
Excel add months to date is a method to add the required number of months to the specified date to obtain the same day in the resulting month. And we can use the inbuilt function EDATE to perform the required operation.
Users can use the EDATE() to determine critical dates in a financial calendar, such as the start and end dates of the fiscal quarters and maturity dates.
For example, the table below shows date values in column A, entered using the Excel DATE function.
And the requirement is to use the dates and months provided in columns A and B to obtain the final dates in column D based on the descriptions specified in column C. Assume the data format of column D is also Date, just as in column A.
As each date is the first day of the month, we can use the Excel EDATE function in the target cells to perform Excel add months to date start of month.
In the above Excel add months to date start of month example, the EDATE() in each target cell accepts two mandatory argument values. While the first argument is the given date value, the second is the months.
And the function adds or deducts the months from the given date based on whether the months count is positive or negative. So, it returns a date in the month after or before the given date as a sequential serial number. But, as we initially set the data type in the target cells as Short Date in the Home tab, the function return values in the specific cells appear in a valid date format.
Table of contents
Key Takeaways
- The formula to perform Excel add months to date enables one to add the specified total months to a given date value. And the result is a date indicating the same day in the determined month.
- In Excel, users can add months to a date value to determine specific dates, such as periodic loan repayment due dates.
- We can use the EDATE function to add months to a date in Excel. The function accepts two mandatory arguments, start_date and months, as inputs.
- We can use the DATE function instead of the EDATE function to add the specified months to the given date.
Add Months To Date Using EDATE Function
The syntax of the Excel add months to date formula, EDATE(), is as follows:
Where,
- start_date: A valid date value, entered using the DATE() to avoid supplying the date value as a text and the EDATE() returning the Excel #VALUE! error.
- months: An integer indicating the count of months before or after the start_date. While a positive months value results in a future date, a negative value gives a past date. Also, if the supplied value is not an integer, it will get truncated.
Further, the two arguments are mandatory.
The steps to Excel add months to date using EDATE() are as follows:
- First, enter the date value using the DATE() in the source dataset to ensure the date is valid and in the valid date format. Next, ensure the required months’ count is a valid integer.
- Select the target cell and enter =EDATE(. [ Alternatively, type =E or =ED and choose the EDATE function from the Excel suggestions.]
- Enter the two mandatory argument values, separated by a comma, and close the parenthesis.
- Press Enter to view the sequential serial number equivalent of the date value obtained after adding the specified months to the given date in the target cell.
[Alternatively, we can choose the target cell and select the Formulas tab – Date & Time function group drop-down button – EDATE function.
The Function Arguments window will open.
Enter the EDATE() argument values in the corresponding fields, Start_date and Months, in the Function Arguments window. And then click OK to execute the function in the target cell.]
- Right-click the target cell and choose Format Cells from the contextual menu. And then, select the Number tab à Date or Custom category à the required date format in the Format Cells window.
- Click OK in the Format Cells window to view the EDATE() output in the required date format.
Examples
Let us see the Excel add months to date formula examples to use it effectively.
Example #1 – Different Result In Case Of February
The image below shows two lists in columns A and C. While we must fill column A with dates from each month in 2023, column C must contain 2024 dates, from each month, based on the first dates in each column.
Then, we can use the EDATE() in the target cells A3:A13 and C3:C13 to obtain the required outcome.
Also, we enter the first dates in cells A2 and C2 using the DATE(), which accepts three inputs, year, month, and day, and returns a valid date. And with the two cells selected, pressing Ctrl + 1 opens the Excel Format Cells window. It which shows the custom Excel date format of the two cells in the Custom category in the Number tab.
Step 1: Choose cell A3 and enter the EDATE().
=EDATE(A2,1)
Step 2: Press Enter to view the EDATE() output in the target cell.
[Alternatively, we can choose the target cell and select Formulas – Date & Time – EDATE.
The Function Arguments window opens, where we must update the EDATE() argument values in the specified fields.
And click OK to obtain the EDATE() return value in the target cell.]
Step 3: Using the Excel fill handle, enter the formula in the remaining target cells in column A.
Step 4: Select cell C3, enter the EDATE(), and press Enter.
=EDATE(C2,1)
Step 5: Enter the formula in cells C4:C13 using the fill handle.
The output values are the sequential serial number equivalents of the required dates. So, here is how to set the required date format in the target cells.
Step 6: Select cell A2 and choose Home – Format Painter.
Next, hover the cursor over cell A3. And then, while pressing the mouse’s left key, drag the mouse over cells A3:A13 to apply the cell A2 data format to the chosen cells.
Likewise, repeat the process for cells C3:C13 to achieve the following output.
The EDATE() accepts the previous cell date value as the first argument and 1 as the second argument since we require the date in each consecutive month.
We supply the last day of January 2023 and 2024 to the EDATE() in cells A3 and C3. So, the function output will be the last day of the next month, February, of the corresponding year.
However, as 2023 is a non-leap year, and 2024 is a leap year, February has 28 and 29 days in the respective years.
Thus, the formula for Excel add months to date returns different outputs in the case of February based on whether the year in the given date is a leap or non-leap year.
And hence, the subsequent dates or the EDATE() output in the remaining target cells get altered from 31st, and are the 28th and the 29th in columns A and C, respectively.
Example #2 – Special Due Date For Leap Year
The table below lists the first monthly loan payment dates and the loan amounts.
Also, we enter the date values in column A using the DATE(). And the column A cells’ data format is a custom date format, which we can check as explained in the previous section.
And the requirement is to determine the next monthly loan payment dates in column C based on the given dates in column A.
Then, as the above scenario is an example of Excel add months to date end of month, we can use the EDATE() in the target cells to achieve the required outcome.
Step 1: Choose cell C2 and enter the EDATE().
=EDATE(A2,1)
Step 2: Press Enter to view the EDATE() output.
Step 3: Use the fill handle to update the formula in the remaining target cells.
Step 4: Select cell A2 and choose Home – Format Painter.
Step 5: Hover the cursor on cell C2, and while left-clicking the mouse, drag the mouse over cells C2:C8 to apply the cell A2 data format to the chosen target cells.
Thus, we obtain the required output in the target cells in the required date format.
In the above Excel add months to date end of month example, the EDATE() in a target cell accepts the date value in the corresponding row as the first argument. And the months argument is 1, as we must determine the date in the next month.
And all the given dates are the last day in January of different years, and the function in the target cells adds the count of months, 1, to the month in the given date values. Thus, function output is the last day of February in the corresponding years.
Furthermore, while some dates in column C are the 28th of February, others are the 29th of February. The reason for the difference is that February has 29 days in leap years and 28 otherwise.
Thus, the function output in the leap years shows the required last day in February as 29, making them a special case.
Example #3 – Get Previous Months With Negative Number
We can supply a negative value as the months argument. It will make the EDATE() deduct the specified count of months from the given date to result in a date from the resulting past month.
For example, the table lists the current dates and total months worked data in columns A and B.
The requirement is to display the joining dates in the past in column C based on the given data.
Also, the column A and C cells have the custom date format, highlighted in the Custom category in the Number tab in the Format Cells window.
Step 1: Select cell C2, enter the EDATE(), and press Enter.
=EDATE(A2,B2)
Step 2: Using the fill handle, update the formula in the remaining target cells.
Let us check the cell C6 formula to understand how the function works.
The EDATE() accepts 29-Jul-23 as the start_date and -8 as the months. So, it deducts eight months from the given date to result in a past date, the 29th of November in 2022.
Example #4 – Other Ways To Add Months To Date In Excel
We can use other inbuilt functions to add months to a date value in Excel.
For example, let us see how to use the DATE() to perform Excel add months to date minus 1 day.
The table below shows the projects at a firm, their start dates and duration in months.
And the requirement is to determine the project submission dates based on the given project start dates and durations. And the date must be one day before the day specified in column B.
Assume the target cells are in column D, with the column D cells having the same custom date format as column B cells. And we can confirm the same by choosing cells B2:B6 and D2:D6 and pressing Ctrl + 1 to access the Format Cells window. And the window will show the chosen cells’ data format as the custom date format in the Custom category in the Number tab.
We can use the DATE() instead of the EDATE() in the target cells to add the specified months to the dates in the corresponding rows.
Step 1: Choose cell D2, enter the DATE(), and press Enter.
=DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)-1)
Step 2: Using the fill handle, update the formula in cells D3:D6.
Let us see the cell D6 expression to understand how the formula works in the above Excel add months to date minus 1 day example.
First, the Excel YEAR function accepts the sequential serial number equivalent of the specified date, 16-Jul-23, to return the year 2023. Next, the Excel MONTH function accepts the sequential serial number equivalent of the given date to return the month, 7. And then, the cell C6 value, 30, gets added to 7 to return the value of 37. Next, the Excel DAY function accepts the sequential serial number equivalent of the given date and returns the day 16. And then, the value of 1 gets deducted from 16 to result in the value of 15.
Thus, the DATE() inputs are 2023, 37, and 15, as the year, month, and day. And it returns the date 15-Jan-26.
Please note that the month argument value is 37. So, Excel adds 36 months, which equals 3 years, to the year value. And hence the year becomes 2026, and the remaining one month out of the 37 months displays as Jan in the output.
Example #5 – EDATE With Other Functions
We can use the EDATE() with other functions for practical results.
For example, the table below lists the order numbers, the ordered fruits and the order dates.
We update the order dates in column C using the DATE() and set the data format as a custom date format using the Format Cells window, as explained in the previous examples.
And the task is to calculate the total number of orders between the 1st of May 2023 and the 1st of September 2023 and display the output in cell E2.
Then, we can use the EDATE() with Excel COUNTIFS function and the DATE() in the target cell to obtain the required output.
Step 1: Select cell E2, enter the following formula, and press Enter.
=COUNTIFS(($C$2:$C$16),”>=”&DATE(2023,5,1),($C$2:$C$16),”<“&EDATE(DATE(2023,5,1),4))
First, the two instances of the DATE() accept 2023, 5, and 1 as the year, month, and day argument values. And they return the sequential serial number equivalent of the 1-May-2023, 45047.
Next, EDATE() accepts the sequential serial number equivalent of the date 1st of May 2023, 45047, as the first argument value. And the second argument is 4. So, it adds the 4 months to the given date to return the sequential serial number equivalent of the resulting date 1-Sep-2023, 45170.
And then, the COUNTIFS() checks for cells in the cell range C2:C16 where the dates are greater than or equal to 1-May-2023. In this case, the first condition holds in cells C6:C16.
Next, the COUNTIFS() checks the cell range C2:C16 for cells where the first condition holds, and the dates are less than 1-Sep-2023. In this case, the second condition holds in the cells C6:C12. And hence, the function returns the value 7.
Please note that the first condition in the COUNTIFS() was TRUE in some cells in the specified cell range B2:B16. And that is why the function checked the second condition. Otherwise, the function return value would be 0.
Important Things To Note
- When using the EDATE() to perform Excel add months to date, supply the date value to the EDATE function using the DATE() to ensure it is valid and in a valid date format. Otherwise, the function will return the #VALUE! error.
- Consider that EDATE()’s second argument is not an integer. Then, the value will get truncated.
- Consider that the EDATE() output must be the last day of February. Then, it will return the date, the 29th of February, for leap years and the 28th for non-leap years.
- The EDATE() accepts and returns the sequential serial number equivalents of the supplied and resulting date values.
Frequently Asked Questions (FAQs)
We can add 36 months to a date in Excel using the following EDATE() syntax:
=EDATE(start_date,36)
You can add months to a date in Sheets using the EDATE(). And the steps are as follows:
1. Select a target cell.
2. Enter =EDATE(.
3. Enter the valid date value and the number of months to add to the specified date, separated with a comma. Otherwise, supply the cell references to the two values, separated with a comma.
4. Close the parenthesis and press Enter to view the EDATE() output.
We can add months to a date in Excel VBA using WorksheetFunction.EDate() method:
For example, the table below lists dates entered using the DATE() and the number of months.
Then, here is how to use Excel VBA to apply the EDATE() in the target cells C2:C6 to add the given months to the specified dates in each row.
Please note that the target cells in column C have the same custom date format as column A.
1: With the active worksheet open, press Alt + F11 to open the VBA Editor.
2: Choose the required VBAProject and select the Module option under Insert in the menu to open a new module window.
3: Enter the VBA code in the module to apply the EDATE() in the target cells.
4: Press the play icon on the top menu to execute the code.
Finally, open the active worksheet to find the EDATE() executed to the target cells. And the results are date values in the chosen custom date format and obtained by adding or deducting the specified number of months to or from the given dates.
Download Template
This article must be helpful to understand the Excel Add Months To Date, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Excel Add Months To Date. Here we learn how to add months to date using EDATE function with examples and points to remember. You can learn more from the following articles –
Leave a Reply