A few words of thanks would be greatly appreciated.

Excel - Converting & then copying date value


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.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.


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).