Sorting date ranges

**cclayton** · 12-07-2010, 09:06 AM

I am new to this site and am hoping you can help me with an excel sorting issue. I have extracted a load of data into excel and it comes through in the following format:

Row Labels
01/08/2010 - 07/08/2010
02/05/2010 - 08/05/2010
03/10/2010 - 09/10/2010
04/07/2010 - 10/07/2010

I would like it to be sorted by the actual date of the first Range as at the moment it is sorting by the number so 01,02,03 but it should be by the first date. Does that make sense? I can find no way of sorting like this so any help you can provide would be great.

Kind regards,

Chris

**dcaraher** · 12-07-2010, 09:20 AM

I am sure there is a more sophisticated method than mine but here is what I would do. I think your data is not formatted as dates. So:

Insert two columns next to your data. In the first column extract your first date using the left function. In your second column extract the second date using the right function.

Then format the new cells as date and you should be able to sort.

**arthurbr** · 12-07-2010, 09:22 AM

Following up on dcaracher's suggestion, you can also extract the dates using text to columns - delimited and using "-" as delimiter. No functions necessary in this case

**MPanter** · 12-07-2010, 09:34 AM

The reason this is happening is that if the entire text "01/08/2010 - 07/08/2010" is in one cell, then that can't be recognised as a date. What you can do is.....
Insert a second Column next to and left of the "01/08/2010 - 07/08/2010" cells.
Copy the same "01/08/2010 - 07/08/2010" into the adjacent new column.
Paint all cells in the original Column
Use the "Find&Select" tool and go to "Replace"
In "Find What" enter "-*" the star AFTER the - is very important.
In "Replace With" DON'T enter anything
Click "Replace All"
Do the same for the new adjacent column but removing all characters BEFORE the "-"
Use the "Find&Select" tool and go to "Replace"
In "Find What" enter "*-" the star BEFORE the - is very important.
In "Replace With" DON'T enter anything
Click "Replace All"
Then paint both Column A&B and replace the remaining "-" character with a space.
Use the "Find&Select" tool and go to "Replace"
In "Find What" enter "-" the dash only.
In "Replace With" DON'T enter anything
Click "Replace All"
Then use AppleBits.com to remove all leading and trailing spaces from all cells - which should leave you with two columns with date format data in.
Then format the columns and sort on column A as you see fit.