Convert dates mm/dd/yyyy to TEXT mmyyyy

[tcluster]

Hi,

I have a date column formatted mm/dd/yyyy which needs to be converted to mmyyyy in TEXT format. I can change date format to mmyyyy, but when I attempt to convert to text it reverts back to original date format of mm/dd/yyyy. Any suggestions?

Many thanks,
TC

 

[Gerald Higgins]

Hi, welcome to the board.

Perhaps this ?

=text(a1,"mmyyyy")

 

[tcluster]

Thank you. I tried that but got an error message about creating a circular reference. And I tried other formulae which failed.
I performed a work-around by copy and pasting my 'mmyyyy' column of dates into Notepad > save as text file > copy and paste column from Notepad into a new column in Excel and that worked.
Cheers,
TC

 

[Gerald Higgins]

You shouldn't enter the formula I gave you into cell A1, that would give you a circular reference.
Put it somewhere else, then if necessary, copy, paste special, values to overwrite your original data with the result of my formula.