Re: Sorting Date Problem

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 05/30/04 

Date: Sun, 30 May 2004 23:01:56 +0200

Hi
file is on the way to you. For all others interested the reason for
this behaviour:
- The sorting column does contain real dates but with two different
years (2003 + 2004). So sorting with this column first shows all 2003
dates and afterwards all 2004 data (but as this column was formated
'MMM-DD' this was not that obvious)
- Inserting a helper column only reflecting month+day helped 

--
Regards
Frank Kabel
Frankfurt, Germany
anonymous@discussions.microsoft.com wrote:
> For Peo and Rag, for some unknown reason, I can't open
> your replies in this forum.
> And for Frank,
> Thanks Frank. Will do shortly.
>> -----Original Message-----
>> Hi
>> now i'm a little bit confused about your spread*** layout. If you
>> like email me your file
>> email: frank[dot]kabel[at]freenet[dot]de
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> Sparky wrote:
>>> I placed [ =month(a1) ] in the first cell and copied it
>>> using autofill. The (a1) changed as each new cell location
>>> changed. All zeros appeared in the "A" column. I then
>>> highlighted the list, starting with A4, the first row of
>>> data, and the last cell of the last column. I selected
>>> Sort, Column A and Column C. The results are two groups of
>>> months, Jan-Dec and Jan-Dec again.
>>>> -----Original Message-----
>>>> Hi
>>>> you have to choose column 'A' as first sort criteria in this
dialog
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>>
>>>>
>>>> Sparky wrote:
>>>>> For some reason, I'm still getting two groups. Here's what
>>>>> I've done with my database, which has header data on three
>>>>> lines.
>>>>> 1. Created a column, "A"
>>>>> 2. Inserted the [ =MONTH(A1) ] in the first cell of
>>>>> the "A" column.
>>>>> 3. Copied (autofill) A1 cell all the way down to the end
>>>>> of the listing.
>>>>> 4. While the first column is highlighted after copying the
>>>>> formula, I Shift/clicked on the last column of the
>>>>> database to highlight all columns of data without the 3
>>>>> lines of headers.
>>>>> 5. Selected DATA/SORT and choose column "C" which is my
>>>>> date column and got two groups of dates. I also tried to
>>>>> sort on the "A" column but still got two group of dates
>>>>> sorted.
>>>>>> -----Original Message-----
>>>>>> Hi
>>>>>> you must sort with this formula column
>>>>>>
>>>>>> --
>>>>>> Regards
>>>>>> Frank Kabel
>>>>>> Frankfurt, Germany
>>>>>>
>>>>>>
>>>>>> Sparky wrote:
>>>>>>> I added a column preceding my first text column and then
>>>>>>> copied the cell formula =month(c6) [c6 is my date column
>>>>>>> to be sorted]down to the end and when I sorted the data, I
>>>>>>> still get two groups of Jan to Dec and the numbers in
>>>>>>> the 'formula' column also run from 1 to 12 in two groups.
>>>>>>> Sparky
>>>>>>>> -----Original Message-----
>>>>>>>> Hi
>>>>>>>> Excel sorts your values according to the date values. That is
>>>>>>>> 1-Jan-2004 come after 3-Feb-2003
>>>>>>>> If you want to sort by month you may add a helper column with
a
>>>>>>>> formula like
>>>>>>>> =MONTH(A1)
>>>>>>>> copy this down for all rows and sort again
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards
>>>>>>>> Frank Kabel
>>>>>>>> Frankfurt, Germany
>>>>>>>>
>>>>>>>>
>>>>>>>> Sparky wrote:
>>>>>>>>> I've got a spread*** that has a column with a custom
>>>>>>>>> date format of mmm-dd[eg: Feb-11] and when I sort the
>>>>>>>>> spread***, I get a group of Jan to Dec and then another
>>>>>>>>> group of Jan to Dec.  Why don't I get
> a 'complete' sort
>>>>>>>>> with all the same months together?
>>>>>>>>
>>>>>>>> .
>>>>>> .
>>>> .
>>
>> .