## 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

EDATEto 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 31^{st}, and are the 28^{th} and the 29^{th} 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 28^{th} of February, others are the 29^{th} 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 **29 ^{th} 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 1^{st} of May 2023 and the 1^{st} 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 1^{st} 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 29^{th}of February, for leap years and the 28^{th}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)

**1. How to add 36 months to a date in Excel?**

We can add 36 months to a date in Excel using the following **EDATE() **syntax:**=EDATE(start_date,36)**

**2. How do I add months to a date in Sheets?**

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.

**3. How to add months to a date in Excel VBA?**

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