"srinivasan" wrote:
I have used DATE(YEAR(B8)-YEAR(B7),MONTH(B8-B7),DAY(B8-B7))
in three adjacent column cells to get the result

Arguably, what you should have done is:

DATE(YEAR(B8+1)-YEAR(B7), MONTH(B8+1)-MONTH(B7), DAY(B8+1)-DAY(B7))

since you want an "inclusive" difference.

But that does not always work anyway. Consider when B8 is 31-10-2008 and B7
is 1-11-1969. It has the same result as when B8 is 30-10-2008. Use Tools
Formula Auditing Evaluate Formula to step through the calculation to see
why.

Moreover, I presume that you formatted the three cells with the custom
formats y, m and d respectively.

But formatting only changes the appearance of numbers. It does not change
the actual value. If all three cells have exactly the same formula, as I
suspect, they all result in the same value. Format the cells as General to
see that number. With your formula, it is probably 14609.

This will give you problems if you reference those three cells in other
computations, expecting just years, months and days respectively.

Whether or not you understand all that, simply follow Pete's suggest with my
embellishment. If you want an "inclusive" difference, compute:

years: DATEDIF(B7,B8+1,"y")

months: DATEDIF(B7,B8+1,"ym")

days: DATEDIF(B7,B8+1,"md")

Caveat emptor: Some MVPs have indicated that DATEDIF is broken in some
updates of Excel 2007. I don't know anything about that.


----- original message -----

"srinivasan" wrote in message
...

Thanks for your response
I have used DATE(YEAR(B8)-YEAR(B7),MONTH(B8-B7),DAY(B8-B7)) in three
adjacent column cells to get the result,

year, month, day separately (not together).I have to use the result year,
month day in a separate formula to

arrive at a solution.Hope I have cleared now..