Sorting dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gerry - 06 Oct 2007 20:13 GMT

I got some advice before but I am still having problems sorting dates. Using
Excel 2003. I have a file of about 1000 records over a time span of 1750 to
1921.

When I sort by what I think is the date the order result is as follows...

1900 to 1921
followed by 1750 to 1899.

I can fix by moving a set of records to the bottom but that doesn't help if
the sort parameter is not the primary one.

Can I convert the date column to anything else to solve my problem.

I am not terribly interested in time between dates etc..

Thanks

Slán

Gerry

Reply to this Message

Tyro - 06 Oct 2007 21:21 GMT

I assume your dates are text. Give us examples of your dates.

>I got some advice before but I am still having problems sorting dates.
>Using Excel 2003. I have a file of about 1000 records over a time span of
[quoted text clipped - 17 lines]
>
> Gerry

Reply to this Message

Pete_UK - 06 Oct 2007 21:24 GMT

Excel's date format only starts at 1900, so any earlier dates must be
in text format and that's where your problem lies. You could convert
all the years to numeric format in a helper column and sort on that,
but you would need to explain the format of your dates for me to
suggest a formula to you - can you post a few examples of your dates?

Pete

> I got some advice before but I am still having problems sorting dates. Using
> Excel 2003. I have a file of about 1000 records over a time span of 1750 to
[quoted text clipped - 17 lines]
>
> Gerry

Reply to this Message

Gerry - 06 Oct 2007 21:30 GMT

Sorry about that..

The format I am using is .... yyyy/mm/dd .. 1901/01/25 .. or 1844/08/14.

I have set up the short date in regional settings to this format.

Dates are very important to properly place a person in the proper century.

Thanks for your help.

Gerry

Reply to this Message

Ron Rosenfeld - 06 Oct 2007 22:24 GMT

>Sorry about that..
>
[quoted text clipped - 7 lines]
>
>Gerry

Given that format, you can probably sort as TEXT.

Excel does not recognize dates prior to 1900, so all of your dates prior to
1900 are TEXT, so far as Excel is concerned.

You can set up a helper column (some column contiguous with the range you wish
to sort) and then you will sort on the helper column.

Assuming your dates are in A2:An, in your helper column, row 2 enter this
formula:

=IF(ISNUMBER(A2),TEXT(A2,"yyyy/mm/dd"),A2)

Copy/drag down as far as needed.

Then sort on the helper column.
--ron

Reply to this Message

Dave Peterson - 06 Oct 2007 22:42 GMT

or just
=text(a2,"yyyy/mm/dd")

=Text() won't change text values like those pre-1900 dates.

> >Sorry about that..
> >
[quoted text clipped - 25 lines]
> Then sort on the helper column.
> --ron

Signature

Dave Peterson

Reply to this Message

Gerry - 07 Oct 2007 13:48 GMT

>>Sorry about that..
>>
[quoted text clipped - 27 lines]
> Then sort on the helper column.
> --ron

Thanks for your help - all of you. The helper column fixed the problem of a
simple date sort and multiple column sorts.

Sorting the original date column simply as test caused problems - some
entries did not keep their original data.

This was possibly caused by the fact that when I first had problems with
entering 1900 dates and asked for help, I then downloaded the xdate fix.
Possibly there is a difference in sections of the actual date data - some
are text and some are dates? Am I correct in my thinking?

Anyway thanks again for all help - I did not want to re-enter the data.

p.s. sorry but I also accidentally replied to a sender.

Gerry

Reply to this Message

Ron Rosenfeld - 07 Oct 2007 13:56 GMT

>Thanks for your help - all of you. The helper column fixed the problem of a
>simple date sort and multiple column sorts.
[quoted text clipped - 12 lines]
>
>Gerry

Glad you've got it working. Thanks for the feedback.
--ron

Reply to this Message

Tyro - 06 Oct 2007 22:51 GMT

If your dates are text and are yyyy/mm/dd, 10 text characters, they will
sort correctly by doing a simple ascending sort. The key word here is text.
You refer to regional settings in control panel. That is for dates that are
in Excel's date format, i.e. date serial numbers (not text). Exactly what do
you have? Pure text or a mixture of text and date serial numbers?

> Sorry about that..
>
[quoted text clipped - 7 lines]
>
> Gerry

Reply to this Message

Paul Hyett - 07 Oct 2007 08:43 GMT

In microsoft.public.excel on Sat, 6 Oct 2007, Gerry
<caoinxxx@eircom.net> wrote :

>I got some advice before but I am still having problems sorting dates. Using
>Excel 2003. I have a file of about 1000 records over a time span of 1750 to
[quoted text clipped - 11 lines]
>
>I am not terribly interested in time between dates etc..

Personally, I use the 'Extended Date Functions' Add-In (I'm also using
Excel 2003).

http://j-walk.com/ss//excel/files/xdate.htm

Signature

Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Reply to this Message

Rate this thread: