date conversion

[petemoss]

I have a list of 4000 dates that are formatted like this (i think in text)

1998-7

I want to convert to month 1st of month and year to look like this

July 1, 1998

please help. Thanks.

 

[Norie]

Select the column/list of dates, goto Data>Text to columns... and on the 3rd(?) step choose YMD from the dropdown.

 

[petemoss]

thanks for your prompt reply but nothing in column changed??

 

[Norie]

It worked for me.

What exactly do you have in the column and where did the data come from?

PS You did choose the right option YMD. I sometimes get that wrong - picking the format I want the date in rather than the one it's currently in.

 

[petemoss]

ok-you were right i picked wrong format but now i have jul-98 (originally 1998-7) but i need it to add the 1st of the month to each year and month to look like this

July 1, 1998

Any suggestions?

 

[T. Valko]

I have a list of 4000 dates that are formatted like this (i think in text)

1998-7

I want to convert to month 1st of month and year to look like this

July 1, 1998

please help. Thanks.

One way is to use a formula in a helper column.

A2 = 1998-7

Entered in B2:

=--(A2&"-1")

Format as Date

Then, you can convert the formulas to constants...

• Select the formulas
• Right click>Copy
• Right Click>Paste Special>Values>OK

Then, you can delete the original data if desired.

 

[Norie]

Pete

You've actually got the date you want - take a look in the formula bar.

All you need to do is format it as you want.

 

[petemoss]

Thank you so much. It worked!!!!

This is why I love this board.

Thanks again.