# Using extreme dates in the future or past

#### Kaz Maslanka

##### New Member
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?

K

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.

Last edited:
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

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!

Replies
6
Views
529
Replies
1
Views
270
Replies
0
Views
199
Replies
4
Views
455
Replies
8
Views
303

1,196,288
Messages
6,014,492
Members
441,823
Latest member
GregG26

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back