Download Article
Download Article
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.
Things You Should Know
- The DATE function makes it easy to increment dates by days, months, or years.
- When editing a formula with the DATE function, you can simply add +1 to the month.
- You can also use the EDATE function to increase the date by one month.
Steps
Download Article
-
Turn on your computer and open a new or existing Excel spreadsheet. -
In any cell, for this example we will use A2, type in you date. Ex. 1/1/2006Advertisement -
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 -
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. -
You will see that this cell now has the calculation desired. -
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.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
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.Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
About This Article
Thanks to all authors for creating a page that has been read 339,944 times.
Advertisement
