Converting & then copying date format

kc - Jan 15, 2010 at 07:56 AM
 Joe - Apr 2, 2012 at 11:31 AM

I wonder if anyone can help? I have a spreadsheet with over 40k records which have the date format & time format like this: 02/01/2003 09:02:00. I have gotten rid of the time element of it by doing the usual 'text to columns' and have ended up with this: 02/01/2003

I want to display the date like this: Jan-03 so have done 'format/cells/custom/mmm-yy' and ended up with the result I want in in each cell, however what is displayed in the formular bar is still: 02/01/2003 and if I try to copy and do a paste special it still copies the original format (02/01/2003) and not Jan-03.

Any ideas would be most appreciated.

Thanks in advance

1 response


Just found the answer to my own question so thought I would post incase anyone else finds it helpful.

Needed to write a formula to convert the date in to the appropriate format, then do a paste special - values only to get rid of the formula in the cell so I could pull it into a pivot table. is the formula to convert a date in this format: 02/01/2003 09:02 to this: Jan-03


Enjoy!! :)
Barnaby Jones
Nov 11, 2010 at 01:09 PM
Worked like a charm....thx.
Wow this worked great!
I used

to convert a date in the brazilian format dd/mm/yyyy to US style mm/dd/yyyy. It works better than