Excel date format

[ylittlejohn]

I have created a custom cell format that allows me to enter a date without entering in the dash. Here is the format 00"-"00"-"00. It works just fine however I've discovered that when I sort the various dates it does not sort them in a to z order like it should. What gives???


[ This Message was edited by: ylittlejohn on 2002-05-09 13:30 ]

[Mark W.]

On 2002-05-09 13:28, ylittlejohn wrote:
I have created a custom cell format that allows me to enter a date without entering in the dash. Here is the format 00"-"00"-"00. It works just fine however I've discovered that when I sort the various dates it does not sort them in a to z order like it should. What gives???


[ This Message was edited by: ylittlejohn on 2002-05-09 13:30 ]

That's because you haven't entered a date... you've entered a 5 or 6-digit integer! Dates must be entered with a "/" or "-" separator. See the Excel Help topic for "Tips on entering dates and times".

[Barry Katcher]

If you are entering the dates as mm-dd-yy, it won't work, because it is sorting first by month, then by day and finally by year. You have to enter as yy-mm-dd.

Why not just use one of the standard date formats?

[Mark W.]

On 2002-05-09 13:56, Barry Katcher wrote:
If you are entering the dates as mm-dd-yy, it won't work, because it is sorting first by month, then by day and finally by year. You have to enter as yy-mm-dd.

Why not just use one of the standard date formats?

A cell's format doesn't alter it's content. It's the content the influences calculations and operations such as sorting.

[ This Message was edited by: Mark W. on 2002-05-09 14:52 ]

[Barry Katcher]

Right, Mark. I guess what I meant to say was to use a date format and enter as a date, with the slashes.

[Yogi Anand]

On 2002-05-09 13:28, ylittlejohn wrote:
I have created a custom cell format that allows me to enter a date without entering in the dash. Here is the format 00"-"00"-"00. It works just fine however I've discovered that when I sort the various dates it does not sort them in a to z order like it should. What gives???


[ This Message was edited by: ylittlejohn on 2002-05-09 13:30 ]

To pick up what Mark W is trying to tell us is that just because you changed the format of an entry to look like a date, it does not mean you have entered a date, even though it looks like a date.
So if you have entered a six digit number (as you did), substance wise you have entered six digits ... based on the US customary representation, you may have entered in cell A1
mmddyy ... never mind that you see this representation as a date ... it is not a date to Excel
So if you want to get the setup mmddyy as a date, one of the ways would be to use the following formula ...

=date(right(a1,2),left(a1,2),mid(a1,3,2))

Regards!



_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:20 ]

[ylittlejohn]

Thank you all for the help.

On 2002-05-09 14:24, Yogi Anand wrote:

To pick up what Mark W is trying to tell us is that just because you changed the format of an entry to look like a date, it does not mean you have entered a date, even though it looks like a date.
So if you have entered a six digit number (as you did), substance wise you have entered six digits ... based on the US customary representation, you may have entered in cell A1
mmddyy ... never mind that you see this representation as a date ... it is not a date to Excel
So if you want to get the setup mmddyy as a date, one of the ways would be to use the following formula ...

=date(right(a1,2),left(a1,2),mid(a1,3,2))

Regards!



_________________
Yogi Anand
ANAND Enterprises
http://www.handtech.com/anand yogia@hotmail.com

[ This Message was edited by: Yogi Anand on 2002-05-09 14:26 ]