Hi,
I have these dates in a colum in excel.Need to sort them by date
the below list is "MM/DD/YYY"
Need to sort them as DD/MM/YYY
12/17/2004
12/21/2004
1/4/2005
1/4/2005
1/12/2005
1/12/2005
1/18/2005
Any way to get this.
Regards
Sharath
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi,
I don't think you can sort directly by day first.
You can however add one column for the days and then sort first by day then by the date:
Say the date is in A2:A100
- In a new column, enter the formula to get the day of the date: in B2, =day(A2) and copy/paste along the data
- In menu Data > Sort, sort by column B first, then by column A
Regards,
sebastien
I thought column A was mm/dd/yyyy.
You seem to have accepted the insert a dummy column option therefore (assuming you have presented the list above to the dd/mm/yyyy for the post then you would appear to need the following in your new column:
=DATE(YEAR(E1),MONTH(E1),D
Assuming E contains the mm/dd/yyyy
Chris
Dates in excel are inherantly (beneath the date formatting on the column) stored as a simple number of days since a base date with times in columns sotred as decimal fractions of 1 so date to the left of the decimal as a number of days and times to the right of the decimal point.
This means and date/time column is inherantly sortable into date or time order.
The reason your MM/DD/YYYY formatted column is not sorting is purley becasue excel is following its rules and your PC's local settings for formatting the column.
So to sort any date you need to simply format the dates as a number (in a hidden column or well off to the side of the busineess end of your worksheet so that you can sort by the number column instead of the date formatted column.
Using your example above and creating a second column just by using a formula in column B of +A1 to transfer the "date" into column B and copied the formula down the column.
You then just format column B as a number and you get the results as shown.
A B
1 29/01/2007 39111.00
2 30/03/2007 39171.00
3 30/07/2007 39293.00
4 30/07/2007 39293.00
5 31/10/2006 39021.00
6 31/10/2006 39021.00
As you see this gives you a sequence of numbers that a simple numeric sort will then correctly arrange dates in a correct date sequence regardless of your PC's date settings for US or UK or any other date format.
Hope this helps.
IM
Business Accounts
Answer for Membership
by: chris_bottomleyPosted on 2007-10-25 at 00:53:32ID: 20145767
First thoughts are to either add a new column, (hidden if required) with the DD/MM/YY format and use this to sort or alternatively a macro that converts the original column, applies the sort and then re-instates the display format.
ANy preference?
Chris