Sorting dates

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

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
 
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
 
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
 
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
Click to expand...

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
 
or just
=text(a2,"yyyy/mm/dd")

=Text() won't change text values like those pre-1900 dates.
 
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?
 
In microsoft.public.excel on Sat, 6 Oct 2007, Gerry
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..
Click to expand...
Personally, I use the 'Extended Date Functions' Add-In (I'm also using
Excel 2003).

http://j-walk.com/ss//excel/files/xdate.htm
 
Ron Rosenfeld said:
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
Click to expand...

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
 
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
Click to expand...

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