Excel 2007 : Date formatting problem

**calskin** · 12-03-2009, 01:57 PM

I know this has been asked before, but I can't find a solution to my exact problem.

I have a user who has formatted their dates like so "Nov. 30 09" (no quotes obviously) but I need to change the format to d-mmm-yy.

None of the dates (and there are thousands of them) have a comma separating the day from the year, so I'm wondering if there's a way to correct this so I can change the format?

Thanks in advance.

**DonkeyOte** · 12-03-2009, 02:00 PM

Depends... if the present values are "real" dates and the user has merely applied a Custom Format you can revise said format to your preferred config.

To test, highlight one date and apply a Custom Format to that cell of: d-mmm-yy
(to do this press CTRL + 1 and select Number tab - Choose Custom - enter your format into the "Type" box)

If the appearance of the value does not change then the value in the cell is not a Date value per se and as such will first need to be adjusted.

Let us know.

**calskin** · 12-03-2009, 02:05 PM

Thanks for the fast reply.

It doesn't change. If I retype the date like this Nov 30, 09 instead of Nov. 30 09 the formatting will change. Is there a way I can do this for all the dates at once?

**DonkeyOte** · 12-03-2009, 02:10 PM

Assuming the pseudo-dates are stored in one column you can highlight the dates and run Data -> Text to Columns -> Delimited -> Step 3 choose Date -> MDY and click Finish.

That should "coerce" all string dates to their real Date value equivs. at which point you can apply your format.