format help

[juster21]

I have dates as yyyymmdd stored as text. I'm trying to convert them to mmddyyyy using =text(datevalue(A1),"mmddyyyy") but I get an error (#value!).

Can anybody help?

 

[Norie]

Select the column with the dates.

Goto Data>Text to columns...

On the 3rd step chose Date for Column data format and YMD from the dropdown.

You should now have 'real' dates which you can format as you like.

 

[juster21]

does it matter if these cells start out with the "numbers stored as text" warning?

 

[Norie]

I don't know that one, I'm on 2000.

Did you try my suggestion?

I'm assuming you've got dates that Excel isn't recognising as such and wish to change them to 'real' dates.

 

[juster21]

I've got a number such as 20051213 with the green flag in the corner which says "the number in this cell is formatted as text". I tried your suggestion which does change it to the computer recognized number as a date (35694) but then the conversion doesn't work.

 

[Norie]

What do you mean 'the conversion doesn't work'?

Do you mean when you try the formula you posted?

If you do, why do you need the formula? You now have a 'real' date value which you can format as you like with Cells>Format...

 

[juster21]

Thanks Norie.

Have you ever tried something once and it didn't work and then try it again and it does? Well, it worked the second time so, no, I don't need to use my formula. Thanks again!!