Sort Dates in Excel Spreadsheet


I am trying to use the custom list function to sort a range of dates in a spreadsheet. The dates are formatted as dd/mm/yyyy. However I must be missing somthing as they are sorting in day and not month.

Is there a quicker way of doing this?

With Thanks
Who is Participating?
Davy2270Connect With a Mentor Commented:
It indeed sounds like you need to change the date fields from text into a real date.
A solution that always works is the following:
Enter number 1 in a free cell.
Copy that number.
Select the date column.
Right click your selection and choose Paste Special.
Then choose Values, and the Divide operation.
Click Ok.
Your dates will now be formatted as real dates (in fact dates are numbers, as dates in excel correspond to a number). Sorting will work as it should.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

are the dates really dates? This sounds as if you're sorting text that LOOKS like dates. You will need to transform the text into real dates for a chronological sort.

Can you post a data sample? What regional settings are you using?

cheers, teylyn
SerinaStarAuthor Commented:
Ah you are right....sorry I didn't even think of that. Your advise works perfectly. Thanks very much :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.