Hi Ron,
Thanks for pointing out the bug yet again. This should sort it out again.
=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON
TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " &
(MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR(
A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(A1)<M
ONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY(B1)) ,1,0) & " " &
IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY
(A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(A1
)<MONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY( B1)),DAY(B1),0)
A1 = start date
B1 = end date
I agree with the first one and it should have been 0 1 0 and not 0 0 28.
(Atleast thats my intention). As for the second one, I consider a month to
get over on the same date next month, so it is as intended.
Just saw the post by JE.
<QUOTE
This seems to be self-consistent.
However, it's strange to me that one can't get a result of of 0 1 0, 0 3
0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from
2/28/2005 being less than a month to 3/1/2005 being 1 day more than a
month, when one never had a date that was a month later?
</QUOTE
There was a bug and this one should sort it out.
Regards
Mangesh
Your formula does seem to follow your rules.
But those are not rules that I would use <g.
They give rise to the (to me) illogical results with regard to the month
of
February.
For example: 31 Jan 05 -- 28 Feb 05 to me encompasses a full month; as
does 28
Feb 05 -- 31 Mar 05 (not counting Day 1). Yet the first gives a result of
0 0
28; and the second 0 1 3.
---------------------
If I were using "months" in a count, I think I would devise an algorithm
that
would count full calendar months; followed by the number of days that is
outside that range. So both of my examples above would result in "1
month".
I would either accept that the days out of range might total to more than
31,
or arbitrarily set 30 as the number of days in that pseudo-month.
Another alternative would be to arbitrarily assume 30 day months and 360
day
years. Which is what financial institutions did, and some probably still
do.
And I'm sure others would find illogical results with my "rules" :-)
--ron
|