Date Range Question

[Guest]

Hello.

We have a spreadsheet where the data in column A are dates. Some of the
entries, however, have been put in as date ranges in one cell (i.e., 01/01/06
- 01/11/06). We need to be able to still sort the list by date. Can cells
that have a date range entered still be formatted to read as a date so the
sorting includes them?

This is an example of what we have:
12/04/05
12/06/05
01/01/06 - 01/11/06
12/12/05
01/12/06
01/01/05

This is what we need to sort to without splitting the column:
01/01/05
12/04/05
12/06/05
12/12/05
01/01/06 - 01/11/06
01/12/06

Thank you in advance for any suggestions.

 

[Roger Govier]

Hi

I would use a helper column to sort by. In a spare column enter
=--IF(LEN(A1)<11,A1,LEFT(A1,FIND("-",A1)-1))
Copy down for the range of data involved.
Mark your block of data including the helper column created, and sort by
the helper column.

 

[Guest]

Roger,
Thank you for the suggestion. Yes, that does work and a good solution.
However, I think it may be a bit complicated to explain to the user who has
approached me with the question. : )

I've given him the simpler solution of just having another column with the
ending date range. Gives him the same look he wants and will retain the date
formatting for sorting. He's happy with that.

However, if anyone else has any other easy ideas, I'm still open for
suggestions!
Have a great weekend.
Elf