# Thread: \$700 Trillion (2003 SP2)

1. ## \$700 Trillion (2003 SP2)

I read online that the rather large sum of \$700 trillion can be expressed in the form \$12 million (just under that) for every day between 4 July 1776 and today.

This piqued my curiosity . . . . How does one work out the formula when Excel doesn't really wish to recognize existence before 1 Jan 1900?

Ann

2. ## Re: \$700 Trillion (2003 SP2)

The number of days between 4 July 1776 and 24 September 2008 is the same as that between 4 July 1976 and 24 September 2208. Excel can calculate the latter.

3. ## Re: \$700 Trillion (2003 SP2)

Correct as always; but one must ensure that the time spanned includes the same number of centuries that are divisible by 400. As the century years are leap years only if divisible by 400.

4. ## Re: \$700 Trillion (2003 SP2)

Do you think it would make a significant different in the amount per day if the number of days was off by one or two?

5. ## Re: \$700 Trillion (2003 SP2)

I don't think it would make much difference, but it wouldn't be diffult to offset 400 years instead of the 200 years you suggested. Using 400 years also keeps the day of week the same, as a general procedure all you have to do is manually subttract 400 from the year after you do your calcs... (or even create formulas which display them as desired...)

Steve

6. ## Re: \$700 Trillion (2003 SP2)

It all depends; If you happened to be a lawyer or an accountant, i imagine that it would. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

7. ## Re: \$700 Trillion (2003 SP2)

Don't forget to take Leap seconds into account!

8. ## Re: \$700 Trillion (2003 SP2)

<img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

9. ## Re: \$700 Trillion (2003 SP2)

Excel VBA accepts dates back to 1/1/100, assuming the use of the current calendar system, and I suspect that is what Hans used to figure out the 84,818 day difference.

Sub datecalc2()
Dim startdate As Date, enddate As Date

startdate = DateValue("7/4/1976")
' startdate = DateValue("7/4/1776")
enddate = DateValue("9/24/2208")
' enddate = Date ' today, 24, Sept, 2008
Debug.Print DateDiff("d", startdate, enddate)
End Sub

10. ## Re: \$700 Trillion (2003 SP2)

Interesting point to note:

Excel VBA recognizes that 1900 was not a leap year.

Excel erroneously believes that there was a February 29th 1900.

11. ## Re: \$700 Trillion (2003 SP2)

This is a deliberate error in Excel. When Excel was introduced, Lotus 1-2-3 was the dominant spreadsheet application, and it had this bug. It was a very important selling-point for Microsoft that Excel was Lotus-compatible. So Microsoft built the same bug into Excel, and it has remained ever since. See Excel 2000 incorrectly assumes that the year 1900 is a leap year.

VBA is a general programming language (not just for Excel) and Microsoft had no reason to introduce the same error.

12. ## Re: \$700 Trillion (2003 SP2)

And I always thought that correctness was its own, and the ultimate reward. Oh Dear! Commerce rules after all. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Seriously Hans; Thank you, I have wondered about that for years.

13. ## Re: \$700 Trillion (2003 SP2)

Think about if you had beem able to invest one dollar at 3.5% interest on 1 January, CE 100.

?FV(0.035, DateDiff("yyyy", DateValue("1/1/100"), Date), 0, -1, 0)

Depressing, ain't it.

14. ## Re: \$700 Trillion (2003 SP2)

You could try Extended Date Functions I have used it in the past <img src=/S/pun.gif border=0 alt=pun width=22 height=18> to do some calculations and found it quite useful

15. ## Re: \$700 Trillion (2003 SP2)

>>Depressing, ain't it.

Naw
I'd be too old and set in my ways to enjoy it. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•