Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    $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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: $700 Trillion (2003 SP2)

    Don't forget to take Leap seconds into account!

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: $700 Trillion (2003 SP2)

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

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards
    Don

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards
    Don

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards
    Don

Page 1 of 2 12 LastLast

Posting Permissions

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