Using extreme dates in the future or past

[Kaz Maslanka]

Hi, I get an overflow error when trying to use dates in the extreme past or future. Is there anyway to add a .dll or hopefully something simpler to be able to make date calculations 500000 years from now or 500000 years in the past?

Thanks for your help!
K

 

[Worf]

Hi and welcome to the Board

This one will work on the range 0100-9999: The Spreadsheet Page Excel Downloads: Extended Date Functions (XDATE)

 

[MarcelBeug]

First of all you should consider that the current Gregorian calendar has been in use first since 1582, so from that perspective, counting back 500000 years with the current calendar system is rather hypothetical. Second, when looking into the extreme future, further corrections to the current calendar system will be required as the average calendar year length is 365.2425 days while the actual year length is 365.242199 days. Thanks Wikipedia.
And more extreme: Earth has a limited life cycle

I suppose you want to use the current calendar system: well every 4 years is a leap year, except centuries that can not be divided by 400.
You might take advantage of the fact that the calendar repeats itself every 400 years or 146,097 days.
This is because every normal year the weekday shifts 1 day and in a leap year the weekday shifts 2 days.
In 400 years this is 400 days + 400/4 days - 3 days (the 3 non leap year centuries) = 497 or exactly 71 x 7.

So you can use a reference period, e.g. 1/1/2000 through 12/31/2399. (Avoid 1900 as this is considered a leap year due to a bug in the Excel date system).
Example: 1/1/123456 will be the same day as DATE(2000+MOD(123456;400);1;1) or 1/1/2256 which is a Tuesday.

Hope this is helpful.

 

[MarcelBeug]

By the way it's amazing how money will grow over 500,000 years...
Deposit $ 0.10 at 5% yearly and you'll be a miljonair after only 331 years!
After only 5000 years you'll have an amazing
$ 884,087,702,574,236,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00
500,000 gives a #NUM! error.

LOL

 

[Kaz Maslanka]

Hi and welcome to the Board

This one will work on the range 0100-9999: The Spreadsheet Page Excel Downloads: Extended Date Functions (XDATE)

Thank You!

 

[Kaz Maslanka]

By the way it's amazing how money will grow over 500,000 years...
Deposit $ 0.10 at 5% yearly and you'll be a miljonair after only 331 years!
After only 5000 years you'll have an amazing
$ 884,087,702,574,236,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00
500,000 gives a #NUM! error.

LOL

Thank You!