Sorting Dates in format ##/##/## - ##/##/##

[muchacho]

I have a report and in column A I have dates listed in text format as follows:

06/04/2008 - 12/04/2008
20/04/2008 - 26/04/2008
30/03/2008 - 05/04/2008
13/04/2008 - 19/04/2008

How do I go about being able to sort this column so that the date ranges are in the order from earliest to latest?

I'm guessing the format would need to be changed from text format but I'm unsure how to go about it.

Also to point out, the above is how it's displayed when the reports are sent to me on a weekly basis.

Thanks in advance, Steve

[mrice]

In an adjacent column paste

=INT(MID(A1,7,4)& MID(A1,4,2) & MID(A1,1,2))

into B1 and copy down. You can then sort on this new column to give the rows in order of the first date.

[muchacho]

Cheers.

Rather than just go ahead and paste it without actually knowing how it's helped, could you explain in a nutshell what the

=INT(MID(A1,7,4)& MID(A1,4,2) & MID(A1,1,2))

has done to column A?

[muchacho]

I've just tried that now and it's displaying #VALUE!

[NBVC]

=LEFT(A1,10)+0 will extract the first date of each date range... where A1 is location of the date range...

You may have to format the cell as Date to see a date...

[muchacho]

Thanks I've sorted it.

[mrice]

The way that the formula works is to concatenate the year, month and day in that order and create the integer version. The sorting then works like an other numerical sort with the biggest time increments (the years) occupying the highest rank places.

Its similar to the technique for naming files to include a date in the name and getting them to sort correctly by filename.