UDF is miscalculating

[DougStroud]

I have a UDF for calculating age. But it is calculating incorrectly.
I entered the date of 3/4/63 and get an answer of 106, not 43.

Function Age(Birthday As Date)

    Age = Int((Date - Birthday) / 365.25)

End Function

Formula in worksheet cell is age=(3/4/63)

Any ideas of what I am doing wrong?

thanks,

ds

 

[Richard Schollar]

Doug

I assume you have the cell formatted as a date? If so, try amending your UDF as:

Function Age(Birthday As Long) 

    Age = Int((Date - Birthday) / 365.25) 

End Function

Best regards

Richard

 

[Scott Huish]

Why VBA?

This could be done with a formula:

=DATEDIF(A1,TODAY(),"y")

 

[DougStroud]

Hi Hotpepper,
It is part of a class I am taking, and this section is teaching UDFs.

 

[DougStroud]

Hi Richard,
I looked into formatting the cell as a date, but that does not work. It just shows the date, not a calculation retrieving one's age.
The interesting thing is that no matter what date I place in the argument, the answer stays at 106.

 

[Scott Huish]

Put the date in a cell (for this example, use A1)
then your formula would be:
=AGE(A1)

Format this cell as General.

Book1
	A	B	C	D
1	3/4/1963	43
Sheet1

 

[DougStroud]

Thanks HP,
that works. But why can the date not be entered directly into the argument.
This works....
=RndNumber(5,20) alternatively RndNumber(A1, B1)

 

[Scott Huish]

It can be, but try it like this:

=age("3/4/1963"+0)

otherwise you're dividing 3 by 4 by 1963

which is .000382068 which is less than 1, so if formatted as a date is January 0, 1900 which is why you're getting 106.

 

[DougStroud]

HP, thanks.
It works w/o the zero. Are you doing this to force it to a number rather than a string?

 

[Scott Huish]

Yes I was.