Formatting Date for Sorting
Hi:
I have dates entered into a spreadsheet column in many different formats. I want to be able to sort by date, but am not having any luck formatting some dates, and therefore, sorting by date won't work. For instance, I have entered dates as just years (e.g., 1978), ranges of dates (e.g., 05/01/78-06/06/81), and single day dates. Date format will not allow me to format for just year or a range (MS excel 2000). If I format as "Special" and use YYYY, it gives me some odd single-day date (mm/dd/yy) instead of the 4 numeral year. If I format the year as "general", it won't sort along with standard dates. And I have no idea how to include the ranges of dates, so that they, too can be sorted. Any ideas? |
Formatting Date for Sorting
You've got apples, oranges and perhaps a watermelon or two going on here,
with date values, text date ranges and integer year values. You're going to have to decide on how you want to view this data and enter the dates as dates, possibly splitting your date range entries into 2 columns. -- Kevin Backmann "kp" wrote: Hi: I have dates entered into a spreadsheet column in many different formats. I want to be able to sort by date, but am not having any luck formatting some dates, and therefore, sorting by date won't work. For instance, I have entered dates as just years (e.g., 1978), ranges of dates (e.g., 05/01/78-06/06/81), and single day dates. Date format will not allow me to format for just year or a range (MS excel 2000). If I format as "Special" and use YYYY, it gives me some odd single-day date (mm/dd/yy) instead of the 4 numeral year. If I format the year as "general", it won't sort along with standard dates. And I have no idea how to include the ranges of dates, so that they, too can be sorted. Any ideas? |
Formatting Date for Sorting
A year is not a date--it's just a number. Probably around 2000, huh?
05/01/78-06/06/81 is not a date--it's a string. If you want to sort by a column that has a mixture of what you call dates, you'll want to convert them to real dates. And depending on what they are (strings or numbers), you'll have to do the conversion differently, too. For instance, if you have 1978 in a cell (say A2), you could use: =date(a2,1,1) to make this helper cell a date of January 1st, 1979. If you have a string like 05/01/78-06/06/81 in a cell, then you'll want to choose which "date" to use. For instance, I could use: =DATE(MID(A2,7,2),MID(A2,1,2),MID(A2,4,2)) to return May 1st, 1978. Then after all these conversions are done, you can sort by this helper column. ps. I would put one date in each column if I had to span two dates. kp wrote: Hi: I have dates entered into a spreadsheet column in many different formats. I want to be able to sort by date, but am not having any luck formatting some dates, and therefore, sorting by date won't work. For instance, I have entered dates as just years (e.g., 1978), ranges of dates (e.g., 05/01/78-06/06/81), and single day dates. Date format will not allow me to format for just year or a range (MS excel 2000). If I format as "Special" and use YYYY, it gives me some odd single-day date (mm/dd/yy) instead of the 4 numeral year. If I format the year as "general", it won't sort along with standard dates. And I have no idea how to include the ranges of dates, so that they, too can be sorted. Any ideas? -- Dave Peterson |
All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com