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

Nikki - Jul 1, 2010 at 09:45 AM - Latest reply:  haha
- Feb 25, 2013 at 01:16 PM
Hello,

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?


N
See more 

6 replies

Best answer
43
Thank you
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!

Thank you, Mukesh 43

Something to say? Add comment

CCM has helped 1810 users this month

Additional way of formulating the same solution may be...

=TEXT(A1, "mmm. ") & TEXT(A1, "yyyy")
thank you so Much!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 1, 2010 at 02:30 PM
23
Thank you
Format the cell as YYYYMM
10
Thank you
Excel can't handle dates without a day, but the following will convert a text input of "y-mmm" into a workable date:

=DATE(2000+LEFT(A7,FIND("-",A7)-1),MATCH(RIGHT(A7,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1)

Change A7 to whatever is required. You can then format the cell as mmm-yy or however you want to display it.
If the date input is not actually text and Excel has already converted this to a serial date format, this will revert it:
=DATE(2000+DAY(A7),MONTH(A7),1)
Again, change A7 to the relevent cell.