منتديات تقنية » Office applications »
How to sort by date in Excel 2003
ناشر الموضوع: Samuel Murray
Samuel Murray
Samuel Murray  Identity Verified
هولندا
Local time: 14:20
عضو (2006)
أنجليزي إلى أفيقاني
+ ...
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
المملكة المتحدة
Local time: 13:20
برتغالي إلى أنجليزي
+ ...
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
هولندا
Local time: 14:20
عضو (2006)
أنجليزي إلى أفيقاني
+ ...
بادئ الموضوع
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
المملكة المتحدة
Local time: 13:20
برتغالي إلى أنجليزي
+ ...
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
المملكة المتحدة
Local time: 13:20
برتغالي إلى أنجليزي
+ ...
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
هولندا
Local time: 14:20
عضو (2006)
أنجليزي إلى أفيقاني
+ ...
بادئ الموضوع
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
المملكة المتحدة
Local time: 13:20
برتغالي إلى أنجليزي
+ ...
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
الولايات المتحدة
Local time: 07:20
عضو (2012)
تاميلي إلى أنجليزي
+ ...
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
ألمانيا
Local time: 14:20
أنجليزي إلى ألماني
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:

مشرفو هذا المنتدى
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






Trados Business Manager Lite
Create customer quotes and invoices from within Trados Studio

Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.

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 »