1
Thanks

A few words of thanks would be greatly appreciated.

Excel - Converting & then copying date value



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.
1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

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

Learn more about the CCM team

Published by . Latest update on 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 (https://ccm.net/).

0 Comments