Create a Formula to Increase a Date by 1 Month

Microsoft Excel is a powerful program. If you are using it for a calendar or anything else where you might need to find the date exactly 1, 2, or 3 months after another date here is a formula you can use.

Steps

1. Turn on your computer and open a new or existing Excel spreadsheet.
2. In any cell, for this example we will use A2, type in you date. Ex. 1/1/2006
3. In a different cell, for this example we used B2, type in the amount of months cell A2 is to be incremented by. Ex. 5
4. In the last cell type the formula:
   =DATE(YEAR(A2),MONTH(A2)+B2,MIN(DAY(A2),DAY(DATE(YEAR(A2),MONTH(A2)+B2+1,0))))
   replacing what is in bold with the appropriate cells for your spreadsheet.
5. You will see that this cell now has the calculation desired.
6. Another option is to use =EDATE(start_date,months)
   This is a simpler method but can only be used if the Analysis ToolPak add-in is installed.

Tips

• Calculating the Day is complicated due to the varying 30 and 31 day months. This formula will take a date like 10/31/2006 and add 1 month to become 11/30/2006. Whereas, a more simple formula without the MIN function may give the incorrect date of 12/1/2006.

Related Articles

• Create a Calendar in Microsoft Excel
• Calculate the Day of the Week in Excel
• Convert Measurements Easily in Microsoft Excel
• Create Pivot Tables in Excel
• Create a Currency Converter With Microsoft Excel
• Create a Mortgage Calculator With Microsoft Excel

See also

• Use Google Spreadsheets
• Read and Interpret a Spread Sheet
• Edit the Contents of a Spreadsheet Cell
• Create an XL Worksheet for Continued Fractions
• Create a Graph Using a Spreadsheet
• Add a Column in a Pivot Table
• Create a Spirallic Numbers Dataset
• Do Cost Volume Profit Analysis
• Bulldog Through an Intellectually Simple Problem
• Use the Newton Raphson Method of Quickly Finding Roots

Read more ...

What links here

• Group and Outline Excel Data
• Reduce Size of Excel Files