Excel Date convert

Question

I have date column like this

DEC07
SEP2007
SEP2008
JUN10
JUN09

how can I can convert this into MM/YYYY assuming DEC07 is 12/2007?

Answer 1

This converts it to an actual date and not a string that looks like a date:

=IF(LEN(A1)=5,--REPLACE(A1,4,0,"20"),--A1)

Then format the cells:

mm/dddd

Answer 2

With data in A1, in B1 enter:

=MONTH(DATEVALUE("1 " & LEFT(A1,3) & " 2000")) & "/" & IF(LEN(A1)=5,2000+RIGHT(A2,2),RIGHT(A1,4))

and copy down.

The formula will handle both 2-digit years and 4-digit years.