why isn't this formula calculating correctly?
Dave F wrote:
So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact,
a month apart?
Assuming they are in G14 and I6 respectively, perhaps the following
will satisfy your criteria for "a month apart":
datedif(G14,I6) + and(I6=eomonth(I6,0), day(G14)day(I6))
But I wonder if the answer you really want is:
datedif(date(year(G14),month(G14),1), date(year(I6),month(I6),1))
For example, if G14 were 3/30/2006 and I6 were 4/29/2006, do you want
that to be considered a month apart?
On the other hand, if G14 were 3/31/2006 and I6 were 4/1/2006, do you
want that to be considered a month apart? If not, how much of a
difference should be considered "a month apart"? For example, perhaps
the difference must be at least "half a month" for it to be considered
a month apart. But what is "half a month"? Be sure to consider Feb as
well as 30-day and 31-day months.
|