05-19-06 DATES, prior to 1900, Excel 2002, how to set

[JWSM]

I have a project requiring the entering and sorting of files in date order,
prior to 1900 (mostly 1800s to 1950). Is there a patch or process to make
Excel 2002 work with dates prior to 1900, as it does with those after 1900.

;-)
J

 

[Bernard Liengme]

John Walkenbach has just what you need at
http://j-walk.com/ss/excel/files/xdate.htm
best wishes

 

[Bob Phillips]

John Walkenbach's has a free Extended Date Function Add-In that allows dates
from 1-Jan-400 to 31-12-9999. Those functions allow the various calculations
allowed by 'normal' Excel date functions. The site is at:
http://j-walk.com/ss/excel/files/general.htm


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Pete_UK]

I think if you work in the 1904-based date system then you can work
with dates before 1900 - check out Excel Help for more details.

Hope this helps.

Pete

 

[Dave Peterson]

I think you're thinking of working with negative times.

 

[Pete_UK]

Yes, I probably was, Dave - that's why I referred the OP to Excel Help.

Pete

 

[JWSM]

Thank you for the replies and suggested links. I installed the Walkenbach
patch... and can see the benefits for a sum between dates.

Normally when entering a date (after 1900) you have several formatting
options, where by all dates in a selected area (a column in my file) can be
changed. Perhaps I am missing a key issue or don't know the 'help' question
to ask, to have Excel apply this to dates prior to 1900.

For the moment I have changed all dates to 1900+ and selected a shortened
date version (3-Apr-32 or 03/04/32) to hide the century. It's not quite
what I had in mind (for 3 April 1832), but it at least it responds to
Excel's auto functions and can be sorted by date.

John