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?

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?

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