Change month only in a column containing several rows of dates

[John Heckman]

Apologize if this has been solved previously. I am attempting to change the month only in a column of already entered dates in a column.  For example I have 1/2/2018, 1/5/2018, 1/17/2018, 1/20/18 etc. and want to only change the month from Jan to Feb like this: 2/2/18, 2/5/2018 etc.  Any ideas? 

[#2]

lets say your data are in Col A

you can use this formula below

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

then format your cells to date format

[#3]

Hi John,

If we speak only about Jan to Feb, not every month in dates on next one, I'd first apply to your dates custom format

mm/d/yyyy

when Ctrl+H and replace 01/ on 02/

[#4]

Jamil, 

=EDATE(A1,1)

shall work in such case

[#5]

Yes Sergei,

that one is even better.

[#6]

Thanks, but Find and Replace won't work because I have several dates within the column.

[#7 · Accepted solution]

If you select all your dates before Ctrl+H when Replace All shall work, otherwise find (and replace) one by one

[#8]

This works rather easily! Thanks.

[#9]

Hi John,

your select your own comment as best response.

can you please confirm whether the find and replace worked for you?

[#10]

Yes, Jamil this work as well. Thanks.

[#11]

You are welcome.

Thanks for the feedback.

[#12]

I tried to replace jan to feb. (ex. 1/1/19 to 2/1/19). When doing so, the day also changed to 2? I only want to change the month. using 1/ to 2/ will also change the day if it has 1/. How can I only change the month using the replace function? 

[#13]

I use Excel to keep track of my monthly spending and had the same question about easily changing the month.
To explain for other beginners:
My workbook has a sheet for each month, just so I don't have to do a lot of scrolling. Each month I copy and paste to create a new sheet. Then I have to manually change the due date for each item on my sheet (rent, cable, groceries, etc.)
This is such an easy solution, why didn't I think to do this! It worked wonderfully.

thx Sergei

[#14]

@SergeiBaklan, have tried these solutions, I do see some changes, but for the case where I have different months on same sheet, and desire to change only a month in a single column, containing a range of dates, it happens that both the dates changes (in two months, including undesired one. Even though only the desired column was selected)

*Changed a month on September to October on numeral dates accidentally and unable to undo it

  

[#15]

@mhanila0601 

Selecting the dates please check in which format they are shown in formula bar. In my case that's default ISO format even if in grid dates are formatted as mm/dd/yyyy

Make replacement based on default format. I change -08- on -09-