Are you kidding me??? Are you telling me I can't sort format: dddd, mmmm dd, yyyy?

M

mrsinnister

*-:mad: Well, I am completely stuck. My regional settings are in order
I place the aforementioned format into my Custom format list, but
can't find a way to get this column to sort using the Weekday, Mont
Day, Year format. Can ANYONE provide any suggestions?? I mea
anyone?? Did I break some kind of code by posting on a similar proble
with someone else that I wasn't aware of. Do you all just not know, o
you think the solution is so simple as to not warrant a response?
Enlighten me. I have thick skin.-
 
D

dlw

not sure what your problem is, I set up the same format in excel, entered
some dates, and they sorted fine.
 
G

Guest

Hi

Excel stores dates as numbers, so today is 38799.
If you want to sort by bits of the date in a particular order, you'll have
to use helper columns with those 'bits' in - and sort on those.

Andy.

"mrsinnister" <[email protected]>
wrote in message
news:[email protected]...
 
R

Ron Rosenfeld

*-:mad: Well, I am completely stuck. My regional settings are in order.
I place the aforementioned format into my Custom format list, but I
can't find a way to get this column to sort using the Weekday, Month
Day, Year format. Can ANYONE provide any suggestions?? I mean
anyone?? Did I break some kind of code by posting on a similar problem
with someone else that I wasn't aware of. Do you all just not know, or
you think the solution is so simple as to not warrant a response?
Enlighten me. I have thick skin.-*
Click to expand...

It is not clear to me how you want the output sorted.

If what you are storing is really a date, and not the textual representation of
a date, then the cell format has NO relevance to the sort order.

If you want your values sorted by date, from earliest to latest, you just sort
it ascending. If that doesn't work, then your data is NOT really a date, but a
textual representation of same, and you will have to change it. You can prove
this by executing the formula

=ISTEXT(cell_ref)

where cell_ref is the location of an incorrectly sorted date.

If you want to have your output grouped by days of the week, or months, or
something different than sorting in date order, you'll have to post back with
more info.
--ron
 
M

mrsinnister

Ron said:
It is not clear to me how you want the output sorted.

If what you are storing is really a date, and not the textual
representation of
a date, then the cell format has NO relevance to the sort order.

If you want your values sorted by date, from earliest to latest, you
just sort
it ascending. If that doesn't work, then your data is NOT really a
date, but a
textual representation of same, and you will have to change it. You
can prove
this by executing the formula

=ISTEXT(cell_ref)

where cell_ref is the location of an incorrectly sorted date.

If you want to have your output grouped by days of the week, or months,
or
something different than sorting in date order, you'll have to post
back with
more info.
--ron
Click to expand...
*-Ron, you were correct. It was a textual representation of the dates
in the format aforementioned. I had to manually go in to input all as
abbreviated "numeric" dates in order to sort as planned. I just
thought that Excel automatically recognized this text as dates since I
did have it listed in my regional settings as the long date format.
Once that didn't occur, I would assume that since that was the output
desired when I placed the format as my custom number, it would convert
it to numeric as well. None of those worked so I wasted 5 hours
sorting that junk out, then recoding the text. Excel sucks.-*
 
P

Pete_UK

Yes, but if you didn't multi-post you might have seen other replies
earlier that were alerting you to this and not waste 5 hours. You could
have converted the text to the correct form quite easily - e.g. using a
formula like =VALUE( cell ) and copying down, or highlighting the
column and clicking Data | Text-to-Columns and clicking <finish>.

Pete
 
R

Ron Rosenfeld

*-Ron, you were correct. It was a textual representation of the dates
in the format aforementioned. I had to manually go in to input all as
abbreviated "numeric" dates in order to sort as planned. I just
thought that Excel automatically recognized this text as dates since I
did have it listed in my regional settings as the long date format.
Once that didn't occur, I would assume that since that was the output
desired when I placed the format as my custom number, it would convert
it to numeric as well. None of those worked so I wasted 5 hours
sorting that junk out, then recoding the text. Excel sucks.-*
Click to expand...

Well, the good news is that your understanding of how Excel handles data input
has now improved.

There are methods to automatically change the data from text to dates without
rekeying it all in manually, but it sounds as if you are past that now.
--ron
 
Top