dates sorting erroneously

[mozo]

Hi - I am once again experiencing a strange problem in that a column of dates (formatted as dates) are not sorting properly. Months are grouped together, but 5/4/01 is coming AFTER 5/31/01, for example. I notice that when I try to change the format of the date to a format with leading zeros (i.e., 01/01/2001), the cells don't actually change.
Am I doing something wrong here?

THanks

 

[Mark W.]

Is it possible that 5/4/01 is really "5/4/01" (i.e., Text)?
This message was edited by Mark W. on 2002-08-21 08:07

 

[mozo]

Hm, I don't think so. Would the quotes be visible when I click in the cell?

 

[Mark W.]

No. The best way to tell is use the ISTEXT() worksheet function....

=ISTEXT(ref)

...where ref is the cell containing your supposed date value

 

[mozo]

Ah, ok..thanks. But then if they are indeed text, how do I covert them to dates? The regular Format cells-> date doesn't seem to be working.

 

[Mark W.]

Excel formatting never alters the value stored in a cell. You must either apply the proper date format and re-edit the cells or if you have a lot of such cells use the Edit | Text to Columns... menu command.
This message was edited by Mark W. on 2002-08-21 09:28

 

[mozo]

hm ok..yes, there are many cells with dates. i can't seem to find the edit-text to columns command...using excel xp, btw


thanks again

 

[klb]

After selecting you data,

Try

Data - Text to Columns

 

[Seti]

Try looking under Data/Text to Columns.

 

[mozo]

Got it -- thank you!! This was a real eye-opener; I always thought that when I used FORMAT CELLS that I was actually changing the stored data.