CODEWITHSUNDEEP
excel
Pr0no
Pr0no

Reputation: 4099

Month directly from YYYYMM

I have many values formatted as YYYYMM:

201007
201008

How can I directly extract the month from these values?

201007    jul
201008    aug

I have tried numerous ways found online, a.o. 

=TEXT(RIGHT(A2;2)*29;"mmm")

But I have been unsuccesful.

Upvotes: 1

Views: 219

Answers (2)

keepAlive
keepAlive

Reputation: 6655

So, with 201007 in A2, what about doing

=TEXT(DATE(LEFT(A2; 4); RIGHT(A2; 2); 1); "mmm")

which returns Jul

Actually, you first have to convert your input into a native excel date-object by doing DATE(LEFT(A2; 4); RIGHT(A2; 2); 1).

Finaly, using the function TEXT over it will natively extract the wanted information, i.e. the month name.

As explained by Gary's Student, my solution is an heavy-handed approach if your dates are genuine.

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96753

With values in column A, in B1 enter:

=TEXT(A1,"mmm")

and copy downward:

enter image description here

(this assumes that the values in column A are genuine Excel dates.)

Upvotes: 3

Related Questions