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.
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.
Anyway...here is the formula to convert a date in this format: 02/01/2003 09:02 to this: Jan-03
Thanks to Kc for this tip on the forum.
This document, titled « Excel - Converting & then copying date value », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM