# UDF is miscalculating

#### DougStroud

##### Well-known Member
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.

Code:
``````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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Doug

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

Code:
``````Function Age(Birthday As Long)

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

End Function``````

Best regards

Richard

Why VBA?

This could be done with a formula:

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

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

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.

Put the date in a cell (for this example, use A1)
=AGE(A1)

Format this cell as General.
Book1
ABCD
13/4/196343
Sheet1

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)

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.

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

Yes I was.

Replies
1
Views
137
Replies
1
Views
424
Replies
3
Views
117
Replies
0
Views
150
Replies
3
Views
90

1,219,819
Messages
6,150,404
Members
450,960
Latest member
GB2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back