why isn't this formula calculating correctly?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's the formula:

=IF(I$6<$G14,"",IF($F14<0,"N/A",IF(DATEDIF($G14,I$6,"m")/$B$3*$F14>$F14,"",DATEDIF($G14,I$6,"m")/$B$3*$F14)))

I6 is 4/30/2006, G14 is 3/31/2006, F14 is $85,968, B3 is 48

I think it should resolve to 1/48*$85,968 = $1,791. But excel is
calculating it as $0.00.

Thoughts?
 
Dave said:
Here's the formula:
=IF(I$6<$G14,"",IF($F14<0,"N/A",IF(DATEDIF($G14,I$6,"m")/$B$3*$F14>$F14,"",DATEDIF($G14,I$6,"m")/$B$3*$F14)))
I6 is 4/30/2006, G14 is 3/31/2006, F14 is $85,968, B3 is 48

I think it should resolve to 1/48*$85,968 = $1,791. But excel is
calculating it as $0.00.
Thoughts?
Click to expand...

DATEDIF() is returns zero for those two dates. Apparently DATEDIF()
does not consider them to be a month apart.
 
So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact,
a month apart?

Dave
 
Maybe by adding a day to the latest date ?
Or using a combination of MONTH() and YEAR()...

DATEDIF() will return the difference in "full month", but who knows what it
means exactly...
 
Dave F said:
So how do I get Excel to determine that 3/31/2006 and
4/30/2006 are, in fact,
a month apart?

Dave
Click to expand...
DATEDIF is very much an orphan function, see

http://www.cpearson.com/excel/datedif.htm

I was rather taken with Pearson's description:

"DATEDIF has, for whatever reason, been treated as one of the
drunk cousins of the Function Family. Excel knows he lives a
happy and useful existence, and will acknowledge his existence
when you ask, but will never mention him in "polite"
conversation."

http://www.cpearson.com/excel/datedif.htm

I was rather taken with Pearson's description:

"DATEDIF has, for whatever reason, been treated as one of the
drunk cousins of the Function Family. Excel knows he lives a
happy and useful existence, and will acknowledge his existence
when you ask, but will never mention him in "polite"
conversation."


The problem seems to arise in this case because DATEDIF decides
the length of a month on a basis of the first variable and this
leads to problems when the month of the first variable is longer
than that of the second. As far as I can tell, there is no
problem if you change the formula to say
=DATEDIF(A1,B1+2, "m")

This seems to work during leap years and the added 2 does not
invalidate the returned integer.

It's a bit late at night, so I will not be totally surprised if
someone comes up with a contrary answer :-)
 
DATEDIF is not used much since it was not really visible in most versions of
Excel (Nothing on it in Help or "Insert function" dialog)...
 
Here's the formula:

=IF(I$6<$G14,"",IF($F14<0,"N/A",IF(DATEDIF($G14,I$6,"m")/$B$3*$F14>$F14,"",DATEDIF($G14,I$6,"m")/$B$3*$F14)))

I6 is 4/30/2006, G14 is 3/31/2006, F14 is $85,968, B3 is 48

I think it should resolve to 1/48*$85,968 = $1,791. But excel is
calculating it as $0.00.

Thoughts?
Click to expand...

Well, the DATEDIF function was only documented in XL2000. I doubt that it is
an officially supported function.

It gives unexpected results, sometimes, if the number of days in the start_date
month is greater than the number of days in the end_date month. For example,
if you use 31 Jan 2006 and 1 Mar 2006, you will discover it is 1 month and -2
days (that's negative 2 days) apart.

The VBA Datediff function does better in those circumstances.
--ron
 
Hi Dave

See reply posted to your question on 07/10/06 18:13
Re: =DATEDIF(3/31/2006,4/30/2006,"m") equals 0?
 
This is a well-known problem, you are by no means the first.

If the start date has 31 days, Apr/Jun/Sep/Nov all have issues if your start
date is the 31st.

Only Feb has issues if your start date is the 30th.

You can circumvent it by foirving DATEDIF to work on an earlier date



=IF(I$6<$G14,"",IF($F14<0,"N/A",IF(DATEDIF($G14-DAY($G14)+1,I$6-DAY(I$6)+1,"
m")/$B$3*$F14>$F14,"",DATEDIF($G14-DAY($G14)+1,I$6-DAY(I$6)+1,"m")/$B$3*$F14
)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Hi Dave

in your particular scenario, where you are only concerned with
calculating the number of months of depreciation, you might be better to
use 28/01/06, 28/02/06, 28/03/06 etc. for your dates in your month
header row, and Format>Cells>Number>Custom> mmm to give Jan, Feb etc.

Then for your asset acquisition / creation date, always use 28/mm/yy
then the Datedif solution will work fine without the adjustments as
suggested by myself and others.
 
Dave said:
I can't believe I'm the first person to have experienced this.
Click to expand...

Right. And MS probably will not fix the "defect" lest they break
backward compatibility, either with Excel or Lotus. At least, that is
their typical response. The only good that might come from filing a
bug report is that MS will post a known-problem report (aka Knowledge
Base article). (I did not find any for DATEDIF currently.) But even
that is unlikely because DATEDIF seems to be deprecated, as others have
noted.

Be that as it may, ordinarily the benefit of filing yet-another bug
report (on the assumption that others have before you) is that
sometimes the fact that customers repeated encounter the same problem
encourages the manufacturer to do something about the problem. I don't
know if that's true for MS; honestly, I suspect it is not :-(. But it
is true for other products from major computer manufacturers. I know
because I have been on the receiving end of such bug reports, and I
have been involved in the decisions to fix or not to fix them.

But I'm curious: how does Joe User ;-), not an industry partner, file
a "bug report" against an MS product?
 
Dave said:
So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact,
a month apart?
Click to expand...

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.
 
Back
Top