Ask a question Report

Converting date to year and month text [Solved/Closed]

Nikki - Latest answer on Feb 25, 2013 01:16PM
I have a cell date entered as 8-Nov. Excel reads this date as 11/8/2010. The cell entry actually means Nov, 2008. I need to change that cell entered as 8-Nov (and the whole column of dates entered in same fashion) as 200811. How do I do that?
Read more 

moins plus
I'm by no means an Excel expert at all, however, I have come up with another possible solution...

=TEXT(A1, "mmm") & ". " & TEXT(A1, "yyyy")

This will translate 11/08/2008 in cell A1 as Nov. 2008. The day value can be any from 1 through 30 (for the month of November).

Hope it works!

Mukesh- Feb 20, 2012 04:20PM
Additional way of formulating the same solution may be...

=TEXT(A1, "mmm. ") & TEXT(A1, "yyyy")
haha- Feb 25, 2013 01:16PM
thank you so Much!
moins plus
Format the cell as YYYYMM

moins plus
Excel can't handle dates without a day, but the following will convert a text input of "y-mmm" into a workable date:


Change A7 to whatever is required. You can then format the cell as mmm-yy or however you want to display it.

David- Feb 21, 2012 12:11PM
If the date input is not actually text and Excel has already converted this to a serial date format, this will revert it:
Again, change A7 to the relevent cell.
This document entitled « converting date to year and month text » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.