yearfrac format error?

[NonTechie]

I have Excel 2007 and Windows XP.

When I use the yearfrac formula, such as =yearfrac(a5,b5) where a5 and b5
have dates in them, I get a decimal which is the correct answer, the
proportion of a year between the dates, and it is the General number format.
But when I multiply it times 12 to get the months, such as
=12*yearfrac(a5,b5) or =yearfrac(a5,b5)*12, either way, the answer comes up
in a customized date format that I then have to manually change the format
back to General in the Format Cells, Number menu.

Why does it change formats when I change the formula when the answer is
clearly a general number and not a date? How can I stop it from doing that?

Is this an Excel 2007 error?

Thanks.

 

[Fred Smith]

You won't get Microsoft to admit this is an error. It's just Excel being
"helpful".

One workaround is to use Datedif to calculate the number of months
difference. Its results are displayed as General.

Regards
Fred.

 

[NonTechie]

Thanks, Fred. Suspicions confirmed. Excel 2003 did not have this problem. You
gave me a nice workaround. I found the syntax at
http://www.cpearson.com/excel/datedif.aspx.

Interestingly, I can get the months using =datedif(a5,e5,"m") but if I do
anything with that cell formula, like adding +2 or multiplying by *.08, I get
the custom date format again. More of the same error.

How does Microsoft become aware of these types of things so they can fix
them (if possible)?

 

[Rick Rothstein]

You might want to reconsider using the DATEDIF function. It is an
undocumented (and, thus, probably an unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this message as part of a newsgroup question...

**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward. And even if
Microsoft did fix the problem in a subsequent Service Pack, any of your
users who remained at SP2 would be subjected to incorrect result.

 

[NonTechie]

Thanks, Rick. I think I will just put up with yearfrac, having it stand alone
in a cell, and then referring to that cell for subsequent calculations. That
way there are no problems with the format.

By the way, I really appreciate you MVPs. You are all AWESOME and have been
such great help in both Office 2003 and now Office 2007. I wish there was a
similar source for problem solving for Windows 7.