Do you need to add months to a date in Excel?

When you come across time series data analysis such as sales or stock trading.  You have to calculate the period of time and play around with the Excel Date and Time functions.

There are different methods to add months to a date in excel. This blog post explains each one with examples to help you understand how to do it.

In this blog post, you will use built-in functions EDATE, DATE, and learn how to use Power Query to add months to a date.  Also, VBA code or Office Scripts can be used If you need to add months to a date in your automation.

Get your copy of the example workbook used in this post to follow along!

Add Months to a Date with the EDATE Function

Instead of storing dates in the traditional day, month, and year format, the excel application keeps dates as serial numbers. The serial numbers start at 1, which is January 1, 1900 and increase by one for each day.

EDATE function returns the serial number that represents the date that is the indicated number of months before or after a specified date.

The EDATE function is easy to use and can be used to quickly create complex date calculations. It allows you to quickly move forward or backward any number of months.

You can use the EDATE function to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

= EDATE ( start_date, months )

• start_date is the date which you desire a specific number of months before or after.
• months is an integer number either positive or negative. It is the amount of time before or after the start_date in months. If you give a floating decimal number for the months, the formula ignores the decimal part and considers only the integer.

= EDATE ( B3, C3 )

Suppose you have a date in column B and the months in column C, which needs to be added to the date.  You can use the above EDATE formula to get the new date value.

Click and drag the fill handle to apply the formula to the range entire column.

The EDATE function adds the number of months in cell C3 to the date in cell B3. This even works with negative numbers to subtract a number of months from your date.

Add Months to a Date with the DATE Function

The DATE function in Excel is used to calculate and generate dates.

It can be used to add, subtract, and compare dates, as well as create dynamic dates based on user input.

This function is especially useful when dealing with time-based data, such as calculating the number of days between two dates or determining the day of the week for a given date.

Excel also includes a wide array of formatting options to format the output of the DATE function, making it easier to read and interpret.

With its versatility and customization options, the DATE function is an indispensable tool for any Excel user.

The DATE function in Excel takes a year, month, and day component and returns the date as a serial number.

= DATE ( year, month, day )

• year is an integer value for the year value that can include one to four digits.
• month is a positive or negative integer representing the month of the year from 1 to 12 for January to December.
• day is a positive or negative integer representing the day of the month from 1 to 31.

= DATE ( YEAR ( B3 ), MONTH ( B3 ) + C3, DAY ( B3 ) )

You can use the above DATE formula to get the new date with the offset numbers of months.

Click and drag the fill handle to apply the formula to the full range of cells.

The Excel functions YEAR, MONTH, and DAY will get the year, month, and day from cell B3.

You can then add an offset number in column C to the value returned from the MONTH function.

Add Months to a Date with the Power Query Function

Power Query is a data shaping tool that enables users to access, transform and connect data in flexible and powerful ways.

It provides a simple and intuitive interface to perform complex data mining and data analysis tasks with ease. Power Query allows the users to connect to multiple data sources, clean and transform data, and load it into Excel for further analysis.

Power Query also has the ability to add months to a date value.

Assuming you have the data in an excel table, then you can follow the below steps to add months to a date using Power Query.

1. Select your data.
2. Go to the Data tab.
3. Choose From Table/Range in the Get & Transform Data group.

This will open the Power Query Editor window.

4. Go to the Add Column tab of the Power Query editor.
5. Select the Custom Column option to open the Custom Column dialog box.

6. Type New Date in the New Column name input box.

= Date.AddMonths ( [Date], [Months] )

7. Type the above formula in the Custom column formula.
8. Press the OK button.

This uses the Power Query Date.AddMonths function in the new column named New Date. This adds the number of months from the field Months with the date in the field Date.

You can see the new column is added to the Power Query table.

9. Select the Home tab.
10. Press the Close and Load button.

Excel Close the Power Query Editor and will pop up the Import Data dialog box.

11. Choose the Existing worksheet and select cell any empty cell. If you need to import data in a new worksheet you can select the New Worksheet option.
12. Press the OK button.

This will load the new dates with added months to Excel.

Add Months to a Date with VBA

Excel VBA is a programming language based on Visual Basic that can be used to automate tasks.

It allows users to build user forms, create custom functions, and macros to automate repetitive tasks.

You can use VBA to create a macro that adds a given number of months to your date column based on input from the user. You can add a macro to your workbook that will add the month to the date in adjacent columns.

Go to the Developer tab and click on the Visual Basic command to open the visual basic editor in Excel. You can also press the Alt + F11 keyboard combination to open the editor.

In the editor, go to the View tab and select the Module option from the menu. This opens an input area to write your code!

Sub AddMonthsToDate()
Dim selectedRange As Range

'Assigned the selected range to the variable selectedRange
Set selectedRange = Selection

'Get the month as an input from the user to add to your date
AddMonths = InputBox("Please enter the months to add to the Date...", "Add Months to Date")

For Each cell In selectedRange
    ' Check the cell value is a date or not.
    ' If the cell value is not a date, return the text Not a Date and continue the execution
    If IsDate(cell.Value) Then
        cell.Offset(0, 1).Value = DateAdd("m", AddMonths, cell.Value)
    Else
        cell.Offset(0, 1).Value = "Not a Date"
    End If
Next cell
End Sub

Add the above code to your module in the visual basic editor.

When you run the VBA code, it will pop up a user input box to get the number of months from the user to add to the dates.

The code loops through the selected range, and the script checks whether each cell value is a date. It adds months to the date and outputs the result in the adjacent cell.

If the cell value is not a date, the script enters the text Not a Date into the adjacent cell and continues the execution.

This uses the excel VBA DateAdd ( "m", AddMonths, cell.Value ) function in the above script to add months to the date in excel.

• m is a string that signifies you need to add months to the date.
• AddMonths is the user input for the number of months to add. It can be a positive or negative integer.
• cell.Value is the value in each cell of your selection. This must be a date.

After you select your range and run the code, you will have the New Date in the neighboring column in Excel.

Add Months to a Date with Office Scripts

Excel Office Scripts is a newer feature in Excel that allows users to automate and customize their spreadsheets. It can be used to create custom functions, apply conditional formatting, and create data validation rules.

If you have the date in Column B. Need to add the Months in Column C with the date in column B and placed the New Date in Column D.

Go to the Automate tab and select the New Script command to open the Office Scripts Code Editor. This is where you can add the script.

function main(workbook: ExcelScript.Workbook) {

  //getselected range
  let rng = workbook.getSelectedRange();
  let rows = rng.getRowCount();
  let cols = rng.getColumnCount();

  //Checked the number of columns
  if (cols < 2) {
    return;
  };

  //loop through selected cells
  for (let i = 0; i < rows; i++) {
   let dateRange = rng.getCell(i,0)
   let excelDateValue = dateRange.getValue() as number;
   let jsDate = new Date(Math.round((excelDateValue-25569)*86400*1000));
   let addMonth = rng.getCell(i, 1).getValue() as number;
   let newDate = new Date(jsDate.setMonth(jsDate.getMonth() + addMonth));
   rng.getCell(i,2).setValue(newDate.toLocaleDateString())
  }
}

Add the above code to the Code Editor and press the Save button.

Now you can select at least two columns Date and Months in your Excel sheet, select your script AddMonths from the Office Scripts dropdown items in Automate menu and press the Run button.

The code will check your selection, and if you have only selected a single column, it stops running the code. Otherwise, the code will continue and loop through your selected cells and add months to the Date given in the first column.

The code will loop through each row of the selected range. It gets the date value from the first column and the number of months to add from the second column of each row.

//Convert the date as javascript date object jsDate
   let jsDate = new Date(Math.round((excelDateValue-25569)*86400*1000));

The above line of code converts each Excel date into a JavaScript date value.

//added the month to the javascript jsDate object and assigned to newDate javascript date object 
   let newDate = new Date(jsDate.setMonth(jsDate.getMonth() + addMonth));

The above line of code adds the number of months to the JavaScript date.

//Convert javascript date object newDate to a string
   rng.getCell(i,2).setValue(newDate.toLocaleDateString())

This is converted to a date string and added back into Excel.

Conclusions

You should now have a better understanding of how to add months to a date in Excel, and which is the best method for your needs.

The EDATE and DATE functions are convenient ways that simplify the task of adding months to dates in Excel.

The Power Query method is perfect for when you need to add months while transforming and loading your data.

You can use either VBA or Office Scripts if you need to add months to date in your automation projects.

Do you know any other method to add a number of months to a date in Excel? Let me know in the comments section!