Date Format

[Alex]

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.

 

[pdberger]

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]

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]

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]

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]

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.

 

[Gord Dibben]

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 Data>Text to Colmns>Next>Next>Column Data Format>Date>MDY works
for me.

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

Those you first deal with by Edit>Replace

What: th

With: 2008

Replace

Then run though Data>Text to columns as above.


Gord Dibben MS Excel MVP

 

[pdberger]

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