ExcelTips (Ribbon Interface)

# Incrementing Months in Dates

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Incrementing Months in Dates.

Hiren has a series of dates in a worksheet. He would like to increment the month, so that (for instance) August becomes September in the dates. He wonders if there a way this can be done using Find and Replace.

You actually could use Find and Replace to do the changing. For instance, let's assume that the date shown in the cell range is 8/1/10 through 8/31/10. You could do a search for "8/" (without the quote marks) and replace it with "9/" (again, without the quotes).

There are a couple of problems with this approach, however. First, you'll end up making changes that you probably don't want to make. For instance, 8/18/10 is change to 9/19/10 because there are two instances of 8/ in the original value. Second, when you change 8/31/10 to 9/31/10, Excel treats the converted date as text rather than as a date because 9/31 is not a valid date. In addition, you'll need to modify what you are searching for and replacing with based upon how the date is formatted in the cells.

A better solution is to use a formula to do the incrementing. The function you want to use for this purpose is EDATE, as shown here:

=EDATE(A1,1)

The formula works great at incrementing the month. You need to understand how the formula works when the result of incrementing the month results in an invalid date. For instance, if the original date is 8/31/10 and you use the formula, it returns 9/30/10, the last valid day in September.

If you want, instead, to see an error when trying to increment, then the formula can be adjusted to compensate:

=IF(DAY(EDATE(A1,1))<DAY(A1),NA(),EDATE(A1,1))

This formula compares the date of the converted date to the day of the original date, and if they are not the same then it returns an #N/A error.

If you want, instead, to have the invalid date "fall over" to the next month (so that 8/31/10, when incremented, becomes 10/1/10), then you can use a different formula that doesn't rely on EDATE:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

This formula will, interestingly enough, handle the "end of the year wrap" correctly. Thus, an original date in A1 of 12/1/10 will become 13/1/10 which is translated by Excel as 1/1/11.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9265) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Incrementing Months in Dates.

Related Tips:

Organize Your Data! Using the powerful sorting capabilities of Excel can help you get your data into just the order you need. Find out how you can use the full capabilities of sorting to your benefit. Check out ExcelTips: Serious Sorting today!

Diane    21 May 2013, 12:20
There's also an End of Month function (EOMONTH)that will always give you the last day of the month. You will need to format the cell as date.
Syl    17 May 2013, 17:26
@Allen
@David Bonin

gerdami    14 May 2013, 03:56
Don't write 8/29/2013 but 29 Aug 2013.
Thanks.
David Bonin    13 May 2013, 09:56
Here is another version of calculating the last day of the month without relying on any add-ins.

Suppose A1 = 8/20/2013

= date( year( a1 ), month( a1 ) + 1, 0 ) gets you 8/31/2013

= date( year( a1 ), month( a1 ) + 2, 0 ) gets you 9/30/2013

= date( year( a1 ), month( a1 ) + 3, 0 ) gets you 10/31/2013

The "0th" day of a month gets you the last day of the previous month.

Want the third from last working day in August?

= date( year( a1 ), month( a1 ) + 1, -2 ) gets you 8/29/2013
Donna    13 May 2013, 09:32
I use the recopy handle to do this. Select the cell containing the first date, then, in the lower right hand corner of the selected cell, click on the small quare (recopy handle) and drag it down. It will increment days. In order to change it to months, click on the AutoFill Options indicator and select Fill months.
Jennifer Thomas    13 May 2013, 08:37
I do this on my bills spreadsheet every month by simply adding 30 or 31 to the previous month's date, e.g. =A1+30. So for a simple solution, you might try that ...
Juan    13 May 2013, 08:07
Thank you very much for this interesting tip!

 *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What is 3+4? (To prevent automated submissions and spam.)

# Our Company

Sharon Parq Associates, Inc.

# Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Excel Products

Word Products

# Our Authors

Author Index

Write for Tips.Net