May I jump in and try to put into words what Frank has suggested to you, as
a solution to your question.
XL will sort your dates in true chronological order, starting with the year,
then the month, and then by the day, and even to hours and minutes, if they
were included in the cell.
Even though the format (mask) of the cells only *display* the month and day,
XL retains the *complete* date within the cell, as should be evident by
looking in the formula bar.
You requested a sort procedure to bypass the year and sort strictly by the
month.
Frank has suggested a way which uses a "helper" column.
When you select *both* the "helper" column *and* the column with the actual
data in it *together*, THEN sort using the "helper" column as the sort key,
the "helper" column will sort by the month AND DRAG the actual data column
cells along with it, giving you exactly what you asked for.
NOW, the helper column.
Assume the dates are in column A.
Enter the formula in column B, and drag down to copy as far as there is data
in column A.
=MONTH(A1)
The (A1) *will* change as you drag it down the rows, so that the formula
will "read and extract" from the neighboring cell in column A.
Frank's formula will extract *only* the month from the actual data column
cells.
No year or day will be present within the cell, so that XL will see nothing
but the number of the month.
After you've dragged down this formula, you should see *only* numbers in
column B (1 to 12).
This is what XL will see also.
This is what you want to tell XL to sort on.
NOW, select *both* column A *and* column B, so that all the data is within
the selection.
Then click on:
<Data> <Sort>
In the "SortBy" window, click the small down arrow and choose "Column B".
Make sure there's a dot in "ascending", then click <OK>.
You should now have all the one's and two's and so on, all together in
column B, AND, since you had column A selected also, the dates in column A
should now be sorted as you asked ... by the month.
A caveat here.
The days will be random, unless they were in order when you started.
If you would like the days to be sorted *within* the months, you could
change the formula to include the days:
=MONTH(A1)&" - "&DAY(A1)
And do exactly the same as directed above.
You will get a question pop-up when you go to sort, asking to sort as text
or numbers.
Just click the numbers box.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
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
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 spreadsheet that has a column with a custom
date format of mmm-dd[eg: Feb-11] and when I sort the
spreadsheet, 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?
.
.
.
