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. |
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. |
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. |
All times are GMT +1. The time now is 10:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com