Technical forums » Office applications »
How to sort by date in Excel 2003
Thread poster: Samuel Murray
Samuel Murray
Samuel Murray  Identity Verified
Netherlands
Local time: 02:02
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 "Cu... See more
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 ▲ Collapse
 
XXXphxxx (X)
XXXphxxx (X)  Identity Verified
United Kingdom
Local time: 01:02
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
 
Samuel Murray
Samuel Murray  Identity Verified
Netherlands
Local time: 02:02
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]
 
XXXphxxx (X)
XXXphxxx (X)  Identity Verified
United Kingdom
Local time: 01:02
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?
 
XXXphxxx (X)
XXXphxxx (X)  Identity Verified
United Kingdom
Local time: 01:02
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
 
Samuel Murray
Samuel Murray  Identity Verified
Netherlands
Local time: 02:02
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.
 
XXXphxxx (X)
XXXphxxx (X)  Identity Verified
United Kingdom
Local time: 01:02
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...
 
Srini Venkataraman
Srini Venkataraman
United States
Local time: 19:02
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.
 
Rolf Keller
Rolf Keller
Germany
Local time: 02:02
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.
 


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

Moderator(s) of this forum
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

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

How to sort by date in Excel 2003






Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »
Trados Studio 2022 Freelance
The leading translation software used by over 270,000 translators.

Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop and cloud solution, empowering you to work in the most efficient and cost-effective way.

More info »