Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range Question
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range Question
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. -- Regards Roger Govier "elfmajesty" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range Question
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 "Roger Govier" wrote: 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. -- Regards Roger Govier "elfmajesty" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with date range | Excel Discussion (Misc queries) | |||
HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
Date Format Question | Excel Discussion (Misc queries) |