Kioskea
Recherche
Ask a question Report

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

Nikki - Latest answer on Feb 25, 2013 01:16PM
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
Read more 
Answer
+19
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!
Answer
+9
moins plus
Format the cell as YYYYMM

Answer
+8
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:

=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.

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:
=DATE(2000+DAY(A7),MONTH(A7),1)
Again, change A7 to the relevent cell.
This document entitled « converting date to year and month text » from Kioskea (en.kioskea.net) 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.