Converting Date Into a specific text format in Excel

50 views Asked by At

Ok so i have been trying to do this and i pretty much keep on failing :p

Basically what i want to do is take the date 10/1/2013 and turn it into 201310. I tried to concatenate using left and right. tried to make it into text using left and right. Nothing seems to be working. The closest i got was to 20130 by using =CONCATENATE(TEXT(J3,"mmyyyy")) Which turned the date into 102013 than i tried =RIGHT(L3,4)*LEFT(L3,2) and got 20130 which is still incorrect. Any help would be appreciated!

1

There are 1 answers

1
Daniel Hilgarth On BEST ANSWER

You could simply turn around your first example and use the correct string:

=TEXT(J3, "yyyyMM")

Note, I used upper case M. Lower case m means minutes, while upper case M means month.
The CONCATENATE is unnecessary.

Please also note, that this is prone to localization issues, i.e. it depends on the language settings. I am from Germany and I need to use jjjj instead of yyyy.