exceldateexcel-formulaexcel-2016

How to convert a month number (i.e. "12") into month name in Excel?


I've got data in a spreadsheet that's formatted YYYYMM and I need to convert it to Month Year format (i.e. 201406 -> June 2014)

I'm using RIGHT() and LEFT() to pull the corresponding bits from the initial data cells, but I can't figure out how to turn the month number into a month name (i.e. 06 -> June, 01 -> January, etc.)

I've tried TEXT(RIGHT(B2,2),"mmmm") which only returns January regardless of the number inputted.

Ideally this should be easy, so, any thoughts? Thanks.


Solution

  • You can use DATE([Year],[Month],[Day]) passing any values for year and day. Just note that this will make the actual value of the cell different to 12 though.

    Excel will be reading 12 as a date which will be 12 days after 00/01/1900 (this is 0 as a date) returning 12/01/1900 which is in January.

    TEXT(DATE(1,B2,1),"mmmm")