At 16:28 09/05/2008 -0600, Jack D. Lewis wrote:
Brian Barker wrote the following on 5/9/2008 3:55 PM:
Sorry, but DAYS360() won't wash. This function is designed to give
the wrong answer for the difference in days between two dates,
which is apparently needed in some financial calculations. The
original enquirer was using DAYS(), so he must have wanted the
correct answer instead. DAYS360() won't work for that. The
function he needs is called subtraction! It is no doubt simply
because the DAYS() function is unnecessary that it is not provided in Excel.
Brian Barker
I'm not sure how you can claim that this function is designed to
give the wrong answer. The answer given is the correct answer for
the number of days BETWEEN the two dates. Not the number of days
between the two dates plus a day for either the beginning date or ending date.
Well, I didn't claim it was designed to give the wrong answer, but
rather that it was designed to give the wrong answer *for the
difference in days between two dates*. It is simply not designed for
that, but instead to do a fictitious calculation based on the idea
that every month has thirty days (hence the "360", of course - being
the number of days that a year would have in that fictitious
world). Apparently this fiction is (sometimes?) required by
accountants, and this function presumably gives them their right
answer. But it doesn't work in the real world and isn't a solution
to the original enquirer's problem.
You have evidently noticed that it already gives a wrong answer - 114
instead of 115 - for the example dates originally given. But this is
not, as you suggest, merely because DAYS360 is counting neither end
date. (You can check this by giving DAYS360 consecutive days in the
same month: the result is 1 and not 0, as your explanation would
require.) The error of only one (between 114 and 115) is actually
because the extra days (over the standard thirty) in January and
March are almost cancelled out by the missing day in February (29
days in this leap year) between the example dates.
To confirm this, try giving DAYS360 two dates a year apart, e.g. 1
January 2008 and 2009. It gives 360, as it should for its stated
purpose. But the true answer, as given by both DAYS and plain
subtraction, is 366, of course - 2008 being a leap year.
But this is all making a problem out of nothing, in fact. Plain
subtraction: =C4-B4 will do what the original enquirer needs and
converts to and fro between document formats without any problem. As
I said before, that's not to say that he hasn't found a bug, nor that
it hasn't been interesting and enjoyable to investigate it!
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
