Excel - Converting & then copying date value

July 2017



Issue


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.

Solution


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

=TEXT(B2,"mmm")&"-"&TEXT(B2,"yy") 


Thanks to Kc for this tip on the forum.

Related


Published by aakai1056. Latest update on April 5, 2012 at 07:45 AM by aakai1056.
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 (ccm.net).