Date Format

[jogrady]

What is the best way to convert the following collumn into a date format such as 1-2009 (Month-Year):

200901
200807
200808
200809
200810
200811

 

[Smitty]

How's this:

=RIGHT(A1,2)&"-"&LEFT(A1,4)

HTH,

 

[BAlGaInTl]

My solution is about the same, but it returns 1-2009 instead of 01-2009

=RIGHT(A1,2)+0&"-"&LEFT(A1,4)

 

[Ron Coderre]

Using your posted list in A1:A6....try this:

B1: =--TEXT(A1,"0000-00-\0\1")

Use a Custom Number Format for B1: m-yyyy
and copy down through B6.

Is that something you can work with?

 

[BAlGaInTl]

Using your posted list in A1:A6....try this:

B1: =--TEXT(A1,"0000-00-\0\1")

Use a Custom Number Format for B1: m-yyyy
and copy down through B6.

Is that something you can work with?

That is nice because it turns them in to actual dates instead of text.

I will have to remember that one for myself.

 

[Greg Truby]

Annoying that there isn't any YM or MY option in text to columns... otherwise that would be the lickety-split way to skin this cat. Thanks for reminding me how to work numeric constants into that 2nd arg in TEXT(), Ron. I'd completely forgotten that.

 

[jogrady]

Thank you Gentlemen. This was exactly what I needed. It worked like a charm!

 

[Smitty]

Annoying that there isn't any YM or MY option in text to columns... otherwise that would be the lickety-split way to skin this cat. Thanks for reminding me how to work numeric constants into that 2nd arg in TEXT(), Ron. I'd completely forgotten that.

Yup, I'da messed with the actual date conversion, but didn't have time.

Thanks Ron for adding that bit.