software

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.

Hi

I would use a helper column to sort by. In a spare column enter
=--IF(LEN(A1)lt;11,A1,LEFT(A1,FIND(quot;-quot;,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.

--
Regards

Roger Govierquot;elfmajestyquot; gt; wrote in message
...
gt; Hello.
gt;
gt; We have a spreadsheet where the data in column A are dates. Some of
gt; the
gt; entries, however, have been put in as date ranges in one cell (i.e.,
gt; 01/01/06
gt; - 01/11/06). We need to be able to still sort the list by date. Can
gt; cells
gt; that have a date range entered still be formatted to read as a date so
gt; the
gt; sorting includes them?
gt;
gt; This is an example of what we have:
gt; 12/04/05
gt; 12/06/05
gt; 01/01/06 - 01/11/06
gt; 12/12/05
gt; 01/12/06
gt; 01/01/05
gt;
gt; This is what we need to sort to without splitting the column:
gt; 01/01/05
gt; 12/04/05
gt; 12/06/05
gt; 12/12/05
gt; 01/01/06 - 01/11/06
gt; 01/12/06
gt;
gt; Thank you in advance for any suggestions.
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

quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; I would use a helper column to sort by. In a spare column enter
gt; =--IF(LEN(A1)lt;11,A1,LEFT(A1,FIND(quot;-quot;,A1)-1))
gt; Copy down for the range of data involved.
gt; Mark your block of data including the helper column created, and sort by
gt; the helper column.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;elfmajestyquot; gt; wrote in message
gt; ...
gt; gt; Hello.
gt; gt;
gt; gt; We have a spreadsheet where the data in column A are dates. Some of
gt; gt; the
gt; gt; entries, however, have been put in as date ranges in one cell (i.e.,
gt; gt; 01/01/06
gt; gt; - 01/11/06). We need to be able to still sort the list by date. Can
gt; gt; cells
gt; gt; that have a date range entered still be formatted to read as a date so
gt; gt; the
gt; gt; sorting includes them?
gt; gt;
gt; gt; This is an example of what we have:
gt; gt; 12/04/05
gt; gt; 12/06/05
gt; gt; 01/01/06 - 01/11/06
gt; gt; 12/12/05
gt; gt; 01/12/06
gt; gt; 01/01/05
gt; gt;
gt; gt; This is what we need to sort to without splitting the column:
gt; gt; 01/01/05
gt; gt; 12/04/05
gt; gt; 12/06/05
gt; gt; 12/12/05
gt; gt; 01/01/06 - 01/11/06
gt; gt; 01/12/06
gt; gt;
gt; gt; Thank you in advance for any suggestions.
gt;
gt;
gt;