Replacing Month only in a date cell

[stanleykobeblue]

I have a long series of dates that I need to change the month on but not the date. Is there an easy Find/Replace action that can do this? I've tried using the wildcard in a find/replace but it doesn't pick up double-digit dates.

e.g. I want to change this list to this:
6/19/14 to 7/19/14
6/6/14 to 7/6/14
6/3/14 to 7/3/14

My list is really long and I can't seem to find an answer for this seemingly easy task. Thanks in advance!

 

[Joe4]

Insert a blank column next to it and use the EDATE function, i.e.
=EDATE(A1,1)
and copy down for all your entries.
This will add exactly one month to your dates.

You can then use Copy -> Paste Special -> Values to overwrite the original column of dates with the updated dates (and delete the newly inserted column).

 

[XOR LX]

Hi,

Are your "dates" actually a standard Excel date format?

For example, if you test one of them (assumed in cell A1) with:

=A1+0

what result do you get?

Regards

 

[Andrew Poulsom]

If Replace did work wouldn't 6/6/14 become 7/7/14? You could put 30 in a spare cell and copy it to the clipboard. Then select your dates and choose Paste Special|Add.

 

[stanleykobeblue]

This is perfect...thank you!!

Joe4's formula worked the quickest & easiest...thanks again

 

[Rick Rothstein]

This is perfect...thank you!!

Joe4's formula worked the quickest & easiest...thanks again

You did not say how you wanted your date to change when the current day did not exist in the month you wanted to change to (for example wanting to change a January date into a February date where the January date was, say, 1/31/14), so I am just pointing out that Joe4's formula would change 1/29/14, 1/30/14 and 1/31/14 all to 2/28/14... this is probably what you would want, but I am pointing it out for your consideration just in case you did not think that far ahead in your request.

 

[arivera27]

My format is mm/dd/yyyy hh:mm and it's changing my hours to 00:00 and ignoring the hourly increments.

 

[Joe4]

My format is mm/dd/yyyy hh:mm and it's changing my hours to 00:00 and ignoring the hourly increments.

Welcome to the Board!

It looks like EDATE will drop any time piece.
To add it back in, just do this:

=EDATE(A1,1)+MOD(A1,1)