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.
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
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
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