How to sort by date in Excel 2003
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 01:44
Member (2006)
English to Afrikaans
+ ...
Jun 18, 2013

G'day everyone

I have an existing spreadsheet in XLS format with one column of date/time data, and I want to sort the sheet by date. The dates are in this format:

01/02/2012 02:00 PM
01/02/2012 09:00 AM
01/02/2013 09:00 AM
01/04/2013 09:00 AM
01/05/2013 09:00 AM
01/06/2012 09:00 AM
01/06/2012 09:00 AM
01/07/2012 09:00 AM
01/08/2011 09:00 AM

i.e. dd/mm/yyyy hh:mm AM/PM. However, when I format the cells as "Custom" and fill in that date format, and then try to sort the sheet by date, Excel simply sorts it as if it is text (as you can see from the pasted sample). What am I missing? How can I get Excel to recognise this data as a date, and then sort by date?

Thanks
Samuel


Direct link Reply with quote
 

Decipherit  Identity Verified
United Kingdom
Local time: 00:44
Portuguese to English
+ ...
What are you doing? Jun 18, 2013

Can you tell me exactly what you're doing? I copied and pasted your dates onto a clean spreadsheet, clicked on Data>Sort and it worked first time:

01/08/2011 09:00
01/02/2012 09:00
01/02/2012 14:00
01/06/2012 09:00
01/06/2012 09:00
01/07/2012 09:00
01/02/2013 09:00
01/04/2013 09:00
01/05/2013 09:00


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 01:44
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Virgin Excel file Jun 18, 2013

Lisa Simpson, MCIL wrote:
Can you tell me exactly what you're doing? I copied and pasted your dates onto a clean spreadsheet, clicked on Data>Sort and it worked first time...


The reason why it worked for you was because you pasted the data into a virgin Excel file. I already have an Excel file in which the cells are formatted as the incorrect type.

But this gave me an idea -- I copied the column to Notepad, then inserted a new column, marked it as "Custom" and with the appropriate customisation, and pasted it from Notepad. Sadly, Excel still treats the cells as "General" when performing a sort operation.

By the way, your Excel sorted the lines incorrectly -- the first number is day, the second is month (in this particular case), not the other way around. Can you get your Excel to sort it correctly?

Here is some more data -- see if your Excel still recognises it as dates:

12/10/2010 09:00 AM
07/03/2011 09:00 AM
04/10/2011 09:00 AM
11/03/2011 09:00 AM
11/03/2011 09:00 AM
28/06/2011 09:00 AM
03/08/2011 09:00 AM
08/08/2011 09:00 AM
15/09/2011 09:00 AM
19/09/2011 09:00 AM
19/09/2011 09:00 AM
22/09/2011 09:00 AM
22/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
05/10/2011 09:00 AM
10/10/2011 09:00 AM
14/10/2011 09:00 AM
21/10/2011 09:00 AM
28/10/2011 09:00 AM
07/11/2011 09:00 AM
09/11/2011 09:00 AM
10/11/2011 09:00 AM


[Edited at 2013-06-18 12:28 GMT]


Direct link Reply with quote
 

Decipherit  Identity Verified
United Kingdom
Local time: 00:44
Portuguese to English
+ ...
Huh? Jun 18, 2013

Samuel Murray wrote:

By the way, your Excel sorted the lines incorrectly -- the first number is day, the second is month (in this particular case), not the other way around. Can you get your Excel to sort it correctly?



Sorry Samuel, you've lost me. I must confess that my brain is fried by hay fever today but despite the fuzz it all seems in perfect date order to me, assuming we're working with dd/mm/yyyy, as you stated. Do you mean it's currently in U.S. format?


Direct link Reply with quote
 

Decipherit  Identity Verified
United Kingdom
Local time: 00:44
Portuguese to English
+ ...
Apparently yes Jun 18, 2013

Samuel Murray wrote:

Here is some more data -- see if your Excel still recognises it as dates:

12/10/2010 09:00 AM
07/03/2011 09:00 AM
04/10/2011 09:00 AM
11/03/2011 09:00 AM
11/03/2011 09:00 AM
28/06/2011 09:00 AM
03/08/2011 09:00 AM
08/08/2011 09:00 AM
15/09/2011 09:00 AM
19/09/2011 09:00 AM
19/09/2011 09:00 AM
22/09/2011 09:00 AM
22/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
26/09/2011 09:00 AM
05/10/2011 09:00 AM
10/10/2011 09:00 AM
14/10/2011 09:00 AM
21/10/2011 09:00 AM
28/10/2011 09:00 AM
07/11/2011 09:00 AM
09/11/2011 09:00 AM
10/11/2011 09:00 AM


Each cell is still set at Custom dd/mm/yyyy hh:mm and if I ask it to reverse the order I get:
10/11/2011 09:00
09/11/2011 09:00
07/11/2011 09:00
28/10/2011 09:00
21/10/2011 09:00
14/10/2011 09:00
10/10/2011 09:00
05/10/2011 09:00
04/10/2011 09:00
26/09/2011 09:00
26/09/2011 09:00
26/09/2011 09:00
26/09/2011 09:00
26/09/2011 09:00
22/09/2011 09:00
22/09/2011 09:00
19/09/2011 09:00
19/09/2011 09:00
15/09/2011 09:00
08/08/2011 09:00
03/08/2011 09:00
28/06/2011 09:00
11/03/2011 09:00
11/03/2011 09:00
07/03/2011 09:00
12/10/2010 09:00


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 01:44
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
No, you're right... Jun 18, 2013

Lisa Simpson, MCIL wrote:
Sorry Samuel, you've lost me. I must confess that my brain is fried by hay fever today but despite the fuzz it all seems in perfect date order to me...


Rats, you're right... your data is sorted correctly after all.

Still, I have had no luck... even if I copy the data to Notepad and then create a brand new Excel file and first format the cells, when I paste the data, Excel sees it as General again. Grrrrr.


Direct link Reply with quote
 

Decipherit  Identity Verified
United Kingdom
Local time: 00:44
Portuguese to English
+ ...
Don't understand the problem Jun 18, 2013

I can't say I follow what the problem is. I didn't have to format the cells at all when I copied your data. Excel recognised it automatically. I'm pretty busy this afternoon but if I can fix it quickly then feel free to e-mail me the doc and I can sort the data for you. It should just be two clicks of a mouse...

Direct link Reply with quote
 

Srini Venkataraman
United States
Local time: 18:44
Member (2012)
Tamil to English
+ ...
sort by date Jun 18, 2013

The column which you want to be sorted: please check whether the format is date ( format cells->number->date , locale: English ( UK) for dd/mm/yyyy format. Now you sort and it will work.
When it was pasted in the new xl sheet , the system defaults to date ( English-US)
so sorting was a breeze.


Direct link Reply with quote
 

Rolf Keller
Germany
Local time: 01:44
English to German
Not that easy Jun 19, 2013

Lisa Simpson, MCIL wrote:

I copied and pasted your dates onto a clean spreadsheet, clicked on Data>Sort and it worked first time


With my German Excel 2003 this way does NOT work regardless of whether I change the format setting (to Date or to Custom) or not. Probably it depends on the language version of Excel.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

How to sort by date in Excel 2003

Advanced search






WordFinder
The words you want Anywhere, Anytime

WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

More info »
Across v6.3
Translation Toolkit and Sales Potential under One Roof

Apart from features that enable you to translate more efficiently, the new Across Translator Edition v6.3 comprises your crossMarket membership. The new online network for Across users assists you in exploring new sales potential and generating revenue.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search