REPOST: Date format pre-1900.

Hi,

I have a table of immigration ships that have arrived in Australian shores.

Some....okay most.....of the ships arrived before 1900.

I was trying to sort by the arrival date, which is in the format of
DD MMM YYYY, but that didn't work, as there were some 1900 dates.

Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates
don't change to the new style.

Any suggestions on how to utilise the pre-1900 dates, so that Excel can
recognise them.

Cheers in advance.
Craig.
P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable
missed it in the other
1000s of messages sent every day. Cheers.

Maybe you could enter all your dates as text:

'1882/11/23

Including dates since 1900.

You could even convert your real dates to Text with a formula like:

=TEXT(A1,"yyyy/mm/dd")

Anyway you do it, you'll want to be sorting text (not real dates) so that they
sort correctly.



"Craig & Co." wrote:
>
> Hi,
>
> I have a table of immigration ships that have arrived in Australian shores.
>
> Some....okay most.....of the ships arrived before 1900.
>
> I was trying to sort by the arrival date, which is in the format of
> DD MMM YYYY, but that didn't work, as there were some 1900 dates.
>
> Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates
> don't change to the new style.
>
> Any suggestions on how to utilise the pre-1900 dates, so that Excel can
> recognise them.
>
> Cheers in advance.
> Craig.
> P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable
> missed it in the other
> 1000s of messages sent every day. Cheers.

--

Dave Peterson

Hi Craig,
Excel does not recognize any date before 1900, but VBA does.
Keep those dates as text.

You can use John Walkenbach's Extended Dates routines
to work with them arithmetically, but be aware that there are
problems as calendar changes -- I believe that is at least
mentioned on his site.
Extended Date Functions Add-In
http://www.j-walk.com/ss/excel/files/xdate.htm

More information on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Craig & Co." <[email protected]> wrote in message news:42151ca3$0$57503
> I have a table of immigration ships that have arrived in Australian shores.
> Some....okay most.....of the ships arrived before 1900.