Extract Year and Month and format as YYYYMM

[KyleW]

I have a bunch of dates that I need to convert from 6/25/2014 to 201406 and so on.

Basically the following is what I'm looking for (2nd column is my results, which I manually typed). So I need YYYYMM, but I can't figure out a single formula that will give me 201406, 201407, and so on.

Starting ValueNeed formula to convert to this
6/25/2014201406
7/7/2014201407
7/7/2014 201407
4/25/2015 201504
4/22/2038203804
12/17/2039 203912

I know =month(A1) will give me 6, 7, 7, 4, 4 and 12. But I need 06, 07, 04, 04 and 12 instead.

Closest I've gotten is =year(A1)&month(A1) which gives me 20146. I'm pretty sure I need TEXT somewhere in my formula and I've tried several scenarios and I just can't get it to work. I thought it was =year(A1)&text(month(A1,"MM")) but this isn't working for me either.

I also tried: =month(text(A1,"MM")) gives me a value of 1 instead of 06
And: =text(Year(A1)&month(A1),"YYYYMM") gives me 195502 instead of 201406

Please help and thanks in advance.

 

[Debraj]

Hi..
did you tried..
=TEXT(A1,"YYYYMM")

 

[KyleW]

WOW - I was making this more difficult than it needed to be. Thank you very much!

 

[pecoflyer]

Be aware that =TEXT(A1,"YYYYMM") returns text looking like a date, not a real date. Instead of a formula just use the proper formatting. The date will then stay a real date

 

[Pfogman1]

Hi, to convert a date into the YYYYMM result AND in a number format, you can try this formula: =YEAR(cell)*100+MONTH(cell)

 

[pecoflyer]

I doubt the OP is still following this thread after 4 years ....