Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
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? -- Brevity is the soul of wit. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
Dave F wrote:
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? DATEDIF() is returns zero for those two dates. Apparently DATEDIF() does not consider them to be a month apart. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact,
a month apart? Dave -- Brevity is the soul of wit. " wrote: Dave F wrote: 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? DATEDIF() is returns zero for those two dates. Apparently DATEDIF() does not consider them to be a month apart. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
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... -- Festina Lente "Dave F" wrote: 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? -- Brevity is the soul of wit. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
A bug report to Microsoft would be a good idea...
-- Festina Lente "PapaDos" wrote: 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... -- Festina Lente "Dave F" wrote: 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? -- Brevity is the soul of wit. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
I can't believe I'm the first person to have experienced this.
-- Brevity is the soul of wit. "PapaDos" wrote: A bug report to Microsoft would be a good idea... -- Festina Lente "PapaDos" wrote: 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... -- Festina Lente "Dave F" wrote: 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? -- Brevity is the soul of wit. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
"Dave F" wrote in message
... So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact, a month apart? Dave -- Brevity is the soul of wit. DATEDIF() is returns zero for those two dates. Apparently DATEDIF() does not consider them to be a month apart. 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 :-) -- Jim Silverton Potomac, Maryland |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
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)... -- Festina Lente "Dave F" wrote: I can't believe I'm the first person to have experienced this. -- Brevity is the soul of wit. "PapaDos" wrote: A bug report to Microsoft would be a good idea... -- Festina Lente "PapaDos" wrote: 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... -- Festina Lente "Dave F" wrote: 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? -- Brevity is the soul of wit. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
On Sat, 7 Oct 2006 16:50:02 -0700, Dave F
wrote: 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? 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
Hi Dave
See reply posted to your question on 07/10/06 18:13 =DATEDIF(3/31/2006,4/30/2006,"m") equals 0? -- Regards Roger Govier "Dave F" wrote in message ... 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? -- Brevity is the soul of wit. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
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) "Dave F" wrote in message ... Here's the formula: =IF(I$6<$G14,"",IF($F14<0,"N/A",IF(DATEDIF($G14,I$6,"m")/$B$3*$F14$F14,"",D ATEDIF($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? -- Brevity is the soul of wit. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
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 FormatCellsNumberCustom 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. -- Regards Roger Govier "Dave F" wrote in message ... So how do I get Excel to determine that 3/31/2006 and 4/30/2006 are, in fact, a month apart? Dave -- Brevity is the soul of wit. " wrote: Dave F wrote: 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? DATEDIF() is returns zero for those two dates. Apparently DATEDIF() does not consider them to be a month apart. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
why isn't this formula calculating correctly?
Dave F wrote:
"PapaDos" wrote: A bug report to Microsoft would be a good idea... I can't believe I'm the first person to have experienced this. 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? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula for Calculating a Floating Date | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |