yearfrac format error?
 

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.

yearfrac format error?
 

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" wrote in message
...
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.

yearfrac format error?
 

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)?

"Fred Smith" wrote:

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" wrote in message
...
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.

.