what one is mathematically correct

[ss]

Just interested.
I have a formula which subtracts one date from another.
say 31st May - 21st May answer 10
However the answer should include both dates so answer should be 11.
I understand what and why it works that way Its not an issue but what one
would be mathematically correct.

 

[Dave Peterson]

Maybe you can just add 1 to the result:

=a1-a2+1

(format as General)

 

[Ron Rosenfeld]

Just interested.
I have a formula which subtracts one date from another.
say 31st May - 21st May answer 10
However the answer should include both dates so answer should be 11.
I understand what and why it works that way Its not an issue but what one
would be mathematically correct.

I would assume that since the mathematical equation 31 minus 21 equals 10 (31-21=10) that the 10 is
"mathematically correct"

By the way, the NETWORKDAYS ATP or Worksheet function does include both the starting and ending days.

 

[ss]

I would assume that since the mathematical equation 31 minus 21 equals
10 (31-21=10) that the 10 is
"mathematically correct"

By the way, the NETWORKDAYS ATP or Worksheet function does include both
the starting and ending days.

Thanks I did add 1 to correct it, I was just curious as to the mathematics.

 

[Rick Rothstein]

Thanks I did add 1 to correct it, I was just curious

as to the mathematics.

I think you might be confusing the difference between two numbers and
counting between two numbers. Take a simple example of 1 and 5... the
difference is 4, but when counting between them (1,2,3,4,5), there are 5
numbers involved (the count is always one greater than the difference). The
same applies to dates... which method you use depends on what it is you are
trying to do with them.

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein formulated the question :

I think you might be confusing the difference between two numbers and
counting between two numbers. Take a simple example of 1 and 5... the
difference is 4, but when counting between them (1,2,3,4,5), there are 5
numbers involved (the count is always one greater than the difference). The
same applies to dates... which method you use depends on what it is you are
trying to do with them.

Rick Rothstein (MVP - Excel)

To add...
That's similar to counting rows/cols; the count row to row is always
the difference plus 1. So starting at row1 and ending at row5 counts 5
rows (5-1 +1). So starting a project on May 21st and finishing it on
May 31st counts 11 days duration (31-21 +1).<g>

 

[Harlan Grove]

Thanks I did add 1 to correct it, I was just curious as to the mathematics.

Intervals and endpoints. You seem to want to measure endpoints, but
arithmetic is on intervals. The timespan between noon 5 Dec 2011 and
noon 6 Dec 2011 is 24 hours, so 1 day, not 2. Thus Excel's result.To
get endpoints, add 1 to the arithmetic result.

 

[Rick Rothstein]

Hello Harlan,

I usually post over in the various forums now (at least more so than in the
newsgroups), so I might have missed other posts by you, but I have not heard
from you in quite sometime (for more than a year, maybe even two, I think).
I hope all has been well with you. Welcome back! I always used to look
forward to your postings (even when they contained "attacks" against what I
had posted<g>) because there was almost always valuable technical content in
them. Hopefully we will be seeing more of you in future threads.

Rick Rothstein (MVP - Excel)

 

[joeu2004]

Thanks I did add 1 to correct it, I was just
curious as to the mathematics.

Both are "mathematically" correct. It is a question of semantics.

Suppose you were born on 1/1/2011 (A1). How old will you be on 1/1/2012
(A2)?

The correct answer is 1 year; 365 days. So that is =A2-A1.

And usually that is the correct computation for __elapsed__ time. It is the
number of __whole__ days.

But suppose you want to __count__ the number of days in January; that is,
"between" 1/1/2011 (A1) and 1/31/2011 (A2) inclusively.

The correct answer is 31. So that is =A2-A1+1.

And usually that is the correct computation for __counting__ units of time
__inclusively__; that is, including the end points.

By the way, this is a constant source of debate and error. Often people are
vague about which they mean. And it comes up in law and finance all the
time.

For example, if you open an account on 12/4/2011 and close it on 12/5/2011,
are you due 1 or 2 days of interest. (Answer: it depends on bank policy.)

For another example, if you bought stock on 2/5/2009 and you sell it on
2/5/2010, did you hold the stock for "more than year" (short-term gain for
US tax purposes)? According to IRS Pub 550, no. But if you sold it on
2/6/2010, yes. So for the holding purposes, the IRS uses the first formula
above, namely =A2-A1.