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.
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.
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.
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.
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. This will open the Power Query Editor window. 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. Excel Close the Power Query Editor and will pop up the Import Data dialog box. 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! 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. 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. The above line of code converts each Excel date into a JavaScript date value. The above line of code adds the number of months to the JavaScript date. 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!
