Hello

I often work with a spreadsheet in which two of the columns are labelled
Start Date and End Date respectively. The spreadsheet has about 3000 rows
of data and is replaced with a new spreadsheet every fortnight.

My problem is that when I try to sort the rows by one of these "date" colums
I end up getting results that seem only to be sorted by day and then month,
but not the year.

For instance, by sorting the spreadsheet in ascending order by Start Date, I
get the following result for the first ten records:


1/01/2007
1/01/2007
1/02/2007
1/02/2007
1/02/2007
1/03/2007
1/04/2007
1/05/2006
1/06/2006
1/06/2007

As you can see, it is clearly sorting by day and month, but not year. I
have ensured the cells are in date format and have even tried using a couple
of custom formats (d-mm-yyyy and dd-mm-yyyy), but the result is the same. I
have also checked the date settings in the Operating System and made sure
they were set to Australian formatting.

So where to now?

I have read about sort orders in the Help files, but don't seem to have made
sense of all that.

Surely I should be able to sort by date, including the year? Or do I have
to try and create some sort of custom sort order? And, if so, how do I do
this?

Your help would be greatly appreciated!

Thanks,

Joe.

If columns are formats as date ( all cells ) it should sort automatically by
year, however check another setting/options following this procedure :
DATESORT check that box, then click on OPTIONS and review what you have
there. If not solution, then an excel expert friend will help us.

"Joe" wrote:

Hello

I often work with a spreadsheet in which two of the columns are labelled
Start Date and End Date respectively. The spreadsheet has about 3000 rows
of data and is replaced with a new spreadsheet every fortnight.

My problem is that when I try to sort the rows by one of these "date" colums
I end up getting results that seem only to be sorted by day and then month,
but not the year.

For instance, by sorting the spreadsheet in ascending order by Start Date, I
get the following result for the first ten records:


1/01/2007
1/01/2007
1/02/2007
1/02/2007
1/02/2007
1/03/2007
1/04/2007
1/05/2006
1/06/2006
1/06/2007

As you can see, it is clearly sorting by day and month, but not year. I
have ensured the cells are in date format and have even tried using a couple
of custom formats (d-mm-yyyy and dd-mm-yyyy), but the result is the same. I
have also checked the date settings in the Operating System and made sure
they were set to Australian formatting.

So where to now?

I have read about sort orders in the Help files, but don't seem to have made
sense of all that.

Surely I should be able to sort by date, including the year? Or do I have
to try and create some sort of custom sort order? And, if so, how do I do
this?

Your help would be greatly appreciated!

Thanks,

Joe.

Try running the column through the DataText to Columns routine.

DataText to Column"fixed width"NextNextColumn Data FormatDateDMYFinish.


Gord Dibben MS Excel MVP

On Sun, 07 Oct 2007 21:43:21 +1000, Joe wrote:

Hello

I often work with a spreadsheet in which two of the columns are labelled
Start Date and End Date respectively. The spreadsheet has about 3000 rows
of data and is replaced with a new spreadsheet every fortnight.

My problem is that when I try to sort the rows by one of these "date" colums
I end up getting results that seem only to be sorted by day and then month,
but not the year.

For instance, by sorting the spreadsheet in ascending order by Start Date, I
get the following result for the first ten records:


1/01/2007
1/01/2007
1/02/2007
1/02/2007
1/02/2007
1/03/2007
1/04/2007
1/05/2006
1/06/2006
1/06/2007

As you can see, it is clearly sorting by day and month, but not year. I
have ensured the cells are in date format and have even tried using a couple
of custom formats (d-mm-yyyy and dd-mm-yyyy), but the result is the same. I
have also checked the date settings in the Operating System and made sure
they were set to Australian formatting.

So where to now?

I have read about sort orders in the Help files, but don't seem to have made
sense of all that.

Surely I should be able to sort by date, including the year? Or do I have
to try and create some sort of custom sort order? And, if so, how do I do
this?

Your help would be greatly appreciated!

Thanks,

Joe.

Thanks guys for your help. Gord's solution solved the problem!

Thanks again. :)


On 8/10/07 2:49 AM, in article ,
"Gord Dibben" <gorddibbATshawDOTca wrote:

Try running the column through the DataText to Columns routine.

DataText to Column"fixed width"NextNextColumn Data
FormatDateDMYFinish.


Gord Dibben MS Excel MVP

On Sun, 07 Oct 2007 21:43:21 +1000, Joe wrote:

Hello

I often work with a spreadsheet in which two of the columns are labelled
Start Date and End Date respectively. The spreadsheet has about 3000 rows
of data and is replaced with a new spreadsheet every fortnight.

My problem is that when I try to sort the rows by one of these "date" colums
I end up getting results that seem only to be sorted by day and then month,
but not the year.

For instance, by sorting the spreadsheet in ascending order by Start Date, I
get the following result for the first ten records:


1/01/2007
1/01/2007
1/02/2007
1/02/2007
1/02/2007
1/03/2007
1/04/2007
1/05/2006
1/06/2006
1/06/2007

As you can see, it is clearly sorting by day and month, but not year. I
have ensured the cells are in date format and have even tried using a couple
of custom formats (d-mm-yyyy and dd-mm-yyyy), but the result is the same. I
have also checked the date settings in the Operating System and made sure
they were set to Australian formatting.

So where to now?

I have read about sort orders in the Help files, but don't seem to have made
sense of all that.

Surely I should be able to sort by date, including the year? Or do I have
to try and create some sort of custom sort order? And, if so, how do I do
this?

Your help would be greatly appreciated!

Thanks,

Joe.