How to sort dates in excel

How to sort dates in excel

Didn't find your answer?

Sure this used to be quite easy but now using Vista and can't find appropriate option etc.

At the moment, dates are being sorted according to the number of days in the month, i.e. 01/01/07, 01/02/07, 01/03/07...., followed by 02/01/07, 02/02/07 etc

Obviously, I want them sorted 01/01/07, 02/01/07, 03/01/07 etc

Is this anything to do with US style date formatting?

Help!
Confused

Replies (4)

Please login or register to join the discussion.

avatar
By User deleted
16th Jan 2009 09:37

rearrange date fields
All of our date fields are stored in yyyymmdd format so we can easily sort any lists and also easily convert the fields back to 'accountant recognisable dates' (not my words) using:
=date(left(A1,4),mid(A1,5,2),right(A1,2))
where A1 contains our date field of 20090114 and the resulting cell reveals 14/01/2009

Thanks (0)
avatar
By carnmores
05th Nov 2008 15:43

Schollar
by name schollar by nature

Thanks (0)
avatar
By listerramjet
05th Nov 2008 12:15

also, you could try
put a 1 in a blank cell, copy it, and paste special to the cells with the dates in, and select multiply. This will convert the text to numbers that represent the dates. Then format the numbers as dates

Thanks (0)
avatar
By RichardSchollar
05th Nov 2008 09:57

Text
Hi

Sounds to me like your 'dates' are actually text values in the cells (did they by any chance come from another system?). If so, the easiest way to convert them to proper dates is to:

Select the dates (assuming a single column) and go Data>TextToColumns>Next>Next and specify an import date format of Date on the third screen and specify DMY from the drop down, then click Finish.

This will convert these 'dates' to true numeric Excel dates which you can then sort as you wish.

Best regards

Richard

Thanks (0)