Previous Topic Next Topic

Converting & then copying date format

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

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
Related:

1 response

Answer 1 / 1
kc
Jan 15, 2010 at 08:18 AM
Hi,

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

Enjoy!! :)
1
Barnaby Jones
Nov 11, 2010 at 01:09 PM
Worked like a charm....thx.
0
Joe
Apr 2, 2012 at 11:31 AM
Wow this worked great!
I used
=date(TEXT(A238,"yyyy"),TEXT(A238,"dd"),TEXT(A238,"mm"))

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

=DATEvalue(MID(A5,4,2)&"/"&left(A5,2)&"/"&Right(A5,4))
0

Similar discussions

Unsupported signal. Please check device output
S.Agnew -
Yaya68 -

14 responses
Date of birth in words
pinckroje -
TrowaD -

1 response
Convert date of birth into words
prakashdnl9 -
vcoolio -

1 response