DateDiff

I want to use DateDiff function in VBA(UDF) to do following

Date of Birth : A1
Year end Date : B1
check if person has completed 65 years of age before B1

Please help.

I'm not sure you can. DateDiff("yyyy", ....) just returns the difference in the year values (ie. December 31, 1941 and January 1, 2006 are 65 years apart).

You'd be better to use the equivalent of the Excel formula:

=Date(Year(A1)+65,Month(A1),Day(A1)) and check to see if this is greater than or equal to the date in B1. It even works for leap years. :-)

Scott

Try this, you don't need VBA just use the function (formula)

=IF(DATEDIF(A1,B1, "Y")=65,TRUE,FALSE)

What this does is if the person reached the age of 65 by B1 the formula returns True if not it returns False.

Hope this helps.

Garf

Sorry I forgot you wanted to see if the person reached the age of 65 before B1.
Well you have to ask a question, what determines before B1 what point in time do we use. I used today’s date Now(), as follows:

=IF(DATEDIF(A1,NOW(), "Y")=65,TRUE,DATEDIF(A1,B1,"Y"))

This formula finds the age of the person from A1 to now, if the person has reached 65 the formula returns True, if not it finds the age between A1 & B1 and returns the age between the two dates. This way if the person reaches 65 by today you know it, if not you know how old the person is based on the two dated A1-B1.

Hope this helps.