Sorting by Date

[Joe]

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 Mac OS and made sure they were
set to Australian formatting in the International system preference.

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.

 

[Joe]

I've just noticed that the problem is worse than I thought. The 35th record
has a date of:

10/01/2007

Of course, in my list of the first ten I provided earlier (in history), the
35th record (10/01/2007) should have appeared 3rd because it is BEFORE
1/02/2007 (i.e. 1st February 2007).

In other words, it is sorting by day first, then doing month, and never
doing the year.

Surely this can't be right???

 

[JE McGimpsey]

In other words, it is sorting by day first, then doing month, and never
doing the year.

Surely this can't be right???

Sounds like your dates are actually entered as Text. Try copying an
empty cell, selecting your dates, then choosing Edit/Paste Special.
Select the Values and Add radio buttons, then click OK.

 

[Joe]

Thanks for the tip!

It worked, but in doing so it's caused another problem. One of the other
columns counted for me the number of days between the Start and End dates.

SCENARIO
- Lets say column A had the start dates
- And column B had the end dates
- And that column C had the number of days

What I had was a formula in the cells in column C. The formula was =(B2-A2)

This always gave me a numeric value. Now it gives me a date.

EXAMPLE
- Column A has 30/07/2007
- Column B has 30/10/2007
- Previously, Column C had 92 (meaning 92 days)
- Now Column C has 01/04/1900

So, how do I fix this unwanted side-effect of the previous solution?

Also, as an aside, do you have any idea why the cells treated as "text" when
the formatting of those two date columns was clearly set as dates?

*shrugs*

Many thanks for your help!

 

[JE McGimpsey]

- Previously, Column C had 92 (meaning 92 days)
- Now Column C has 01/04/1900

So, how do I fix this unwanted side-effect of the previous solution?

Format column C as General.

Also, as an aside, do you have any idea why the cells treated as "text" when
the formatting of those two date columns was clearly set as dates?

If the cells were formatted as Text when the data was entered, then the
number format doesn't affect the display.

Also, values pasted in from other applications are often pasted in as
Text.

 

[Joe]

Oh, okay, well that explains it then.

Thank you very much for your help.