Sorting Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TheNewGuy - 13 Mar 2008 06:24 GMT

I have a large number of spreadsheets with entries based on dates. The
entries have been made by several people using different date formats. IE
2/3/08, 02/03/2008 etc. When I try to sort them they are sorted in clumps
based on the date format entered. When I format all the date cells to show a
specific date format and then ask it to sort by date the entries are still
grouped based on their original date format and not in one complete group as
per the new format shown. Is there any way to fix this without manually
re-entering many of the dates? Thanks.

Reply to this Message

Bob Phillips - 13 Mar 2008 09:25 GMT

That sounds as though some are not really dates, because dates are stored as
number of days since 1st Jan 1900, so they should sort okay regardless of
the format they show as.

Try selecting the column and do a Data>Text To Columns, and on Step 3 select
a date format. This might correct it.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a large number of spreadsheets with entries based on dates. The
> entries have been made by several people using different date formats. IE
[quoted text clipped - 6 lines]
> per the new format shown. Is there any way to fix this without manually
> re-entering many of the dates? Thanks.

Reply to this Message

Jim May - 17 Mar 2008 01:05 GMT

You might also try converting your dates to Numeric Dates by in a helper column
using =DateValue(A1)
If A1 contains 01/13/2008 < text the the above formula in B1 should produce
39460 --<< Now format as a date

> I have a large number of spreadsheets with entries based on dates. The
> entries have been made by several people using different date formats. IE
[quoted text clipped - 4 lines]
> per the new format shown. Is there any way to fix this without manually
> re-entering many of the dates? Thanks.

Reply to this Message

Rate this thread: