SOLVED
Home

Change month only in a column containing several rows of dates

%3CLINGO-SUB%20id%3D%22lingo-sub-207447%22%20slang%3D%22en-US%22%3EChange%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207447%22%20slang%3D%22en-US%22%3E%3CP%3EApologize%20if%20this%20has%20been%20solved%20previously.%20I%20am%20attempting%20to%20change%20the%20%3CSTRONG%3Emonth%20only%20%3C%2FSTRONG%3Ein%20a%20column%20of%20already%20entered%20dates%20in%20a%20column.%26nbsp%3B%20For%20example%20I%20have%201%2F2%2F2018%2C%201%2F5%2F2018%2C%201%2F17%2F2018%2C%201%2F20%2F18%20etc.%20and%20want%20to%20only%20change%20the%20month%20from%20Jan%20to%20Feb%20like%20this%3A%202%2F2%2F18%2C%202%2F5%2F2018%20etc.%26nbsp%3B%20Any%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-207447%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359393%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359393%22%20slang%3D%22en-US%22%3E%3CP%3EI%20tried%20to%20replace%20jan%20to%20feb.%20(ex.%201%2F1%2F19%20to%202%2F1%2F19).%20When%20doing%20so%2C%20the%20day%20also%20changed%20to%202%3F%20I%20only%20want%20to%20change%20the%20month.%20using%201%2F%20to%202%2F%20will%20also%20change%20the%20day%20if%20it%20has%201%2F.%20How%20can%20I%20only%20change%20the%20month%20using%20the%20replace%20function%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207475%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207475%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%3C%2FP%3E%0A%3CP%3EThanks%20for%20the%20feedback.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207469%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207469%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20Jamil%20this%20work%20as%20well.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207468%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207468%22%20slang%3D%22en-US%22%3EHi%20John%2C%3CBR%20%2F%3E%3CBR%20%2F%3Eyour%20select%20your%20own%20comment%20as%20best%20response.%3CBR%20%2F%3E%3CBR%20%2F%3Ecan%20you%20please%20confirm%20whether%20the%20find%20and%20replace%20worked%20for%20you%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207465%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207465%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20works%20rather%20easily!%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207464%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207464%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20select%20all%20your%20dates%20before%20Ctrl%2BH%20when%20Replace%20All%20shall%20work%2C%20otherwise%20find%20(and%20replace)%20one%20by%20one%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207462%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207462%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20but%20Find%20and%20Replace%20won't%20work%20because%20I%20have%20several%20dates%20within%20the%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207460%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207460%22%20slang%3D%22en-US%22%3EYes%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethat%20one%20is%20even%20better.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207456%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207456%22%20slang%3D%22en-US%22%3E%3CP%3EJamil%2C%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DEDATE(A1%2C1)%3C%2FPRE%3E%0A%3CP%3Eshall%20work%20in%20such%20case%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207455%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207455%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20speak%20only%20about%20Jan%20to%20Feb%2C%20not%20every%20month%20in%20dates%20on%20next%20one%2C%20I'd%20first%20apply%20to%20your%20dates%20custom%20format%3C%2FP%3E%0A%3CPRE%3Emm%2Fd%2Fyyyy%0A%3C%2FPRE%3E%0A%3CP%3Ewhen%20Ctrl%2BH%20and%20replace%2001%2F%20on%2002%2F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207454%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20month%20only%20in%20a%20column%20containing%20several%20rows%20of%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207454%22%20slang%3D%22en-US%22%3E%3CP%3Elets%20say%20your%20data%20are%20in%20Col%20A%3CBR%20%2F%3E%3CBR%20%2F%3Eyou%20can%20use%20this%20formula%20below%3CBR%20%2F%3E%3CBR%20%2F%3E%3DDATE(YEAR(A1)%2CMONTH(A1)%2B1%2CDAY(A1))%20%3CBR%20%2F%3E%3CBR%20%2F%3Ethen%20format%20your%20cells%20to%20date%20format%3C%2FP%3E%3C%2FLINGO-BODY%3E
John Heckman
New Contributor

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? 

11 Replies

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

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/

 

Jamil, 

=EDATE(A1,1)

shall work in such case

 

Yes Sergei,

that one is even better.

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

Solution

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

This works rather easily! Thanks.

Hi John,

your select your own comment as best response.

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

Yes, Jamil this work as well. Thanks.

 

You are welcome.

Thanks for the feedback.

 

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? 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies