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

+43
Helpful
2
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!
Was this answer helpful?  
Additional way of formulating the same solution may be...

=TEXT(A1, "mmm. ") & TEXT(A1, "yyyy")
thank you so Much!
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 1, 2010 at 02:30 PM
+23
Helpful
Format the cell as YYYYMM
+10
Helpful
1
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.