On my Excel07 version, when I type "Feb 5", it formats it to "5-Feb-08", so I
guess that's a change from Excel03.

I guess my observation was that Excel was interpreting the "Feb. 5" as text
rather than a number, and that was the root of the problem...

Thanks for your points, though. I'll have to keep them in mind.

P

"Gord Dibben" wrote:

I should stay out of this because I don't run 2007 and date recognotion may
be different than earlier versions.

Simply entering Feb 5 will return an actual date of February 1, 2005 which
when formatted m/d will show 2/1

I don't think Alex wants that.

With dates entered as text Feb. 5 or Apr. 25

Select and DataText to ColmnsNextNextColumn Data FormatDateMDY works
for me.

Note: won't work for entries like April 25th

Those you first deal with by EditReplace

What: th

With: 2008

Replace

Then run though DataText to columns as above.


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 07:21:00 -0800, pdberger
wrote:

It's the period. That's making Excel think you're typing text, rather than a
number to be formatted as a date. Just type "Feb 5" and you should be fine.

"Alex" wrote:

All I did was type Feb. 5 and format it m/d and it still shows as Feb. 5.
Tried it in two sheets.

"pdberger" wrote:

Alex --

I did try it, and it worked. I wonder if you've entered the date in a way
that makes Excel think it's text, rather than a number?

"Alex" wrote:

Thanks. Did you try this because this is the first thing I tried and can't
get it to work. Maybe I'm doing something wrong.

"pdberger" wrote:

Alex --

Select the cell or range, then click the little button next to the 'number'
formatting on the 'Home' tab. When the 'format cells' window opens, go to
the 'number' tab and click the 'custom' category on the left-hand side.
Then, in the 'type' field, change it to "m/d". You can also start the whole
process by right-clicking on the range.

HTH

"Alex" wrote:

Is there a custom date format that will change dates like Mar. 4 and April
25th to m/d? Anything I try isn't changing the format; I suppose because
Excel doesn't recognize these as a date. I'm using Excel 2007. Thank you.