Calculating age in VBA

[Lucien]

Hi,
First of all a happy new year to all of you.
I want to calculate the age of a person in VBA. In normal Excel in use the Yearfrac function for this. However this seems not to work in VBA as VBA does not recognise this worksheetfunction.
Is there an other method of calculating the exact age when the date of birth and the date of today is given?. Dividing the difference of the two dates by 365 does not give the good result.
Thanks in advance for your replies,
Lucien

 

[Joe4]

I realize that dividing by 365 does not yield accurate results, since it does not account for leap year. However if you divide by 365.25 (for 1 extra day every 4 years), you will either be exact or off by a very small number. That is how we handle the situation here, and it works well for us.

 

[Lucien]

Thanks,
If there is no other method to get the exact result I will use your solution.

Regards,
Lucien

 

[Corticus]

Hi,

VBA has its own Datediff syntax as follows:

Dim FirstDate, SecondDate as Date
Age = DateDiff("yyyy",FirstDate,SecondDate)

edit: This should be a built-in function in XL2000+

HTH,
Corticus
This message was edited by corticus on 2003-01-02 13:58

 

[XL-Dennis]

Lucien,

There is also a worksheet-function available for it, DATEDIF:

http://www.cpearson.com/excel/datedif.htm#Age

Kind regards,
Dennis

 

[Barry Katcher]

Weird! I used the DATE.DIFF function on my mchine, and it's coming up in French. Anyone know why?

3Zenith12.xls
	P	Q	R	S
2
3	1/2/2003	9/22/1942	60ans3mois11jours
4
5
Sheet3

 

[Dave Patton]

On 2003-01-02 14:25, Barry Katcher wrote:
Weird! I used the DATE.DIFF function on my mchine, and it's coming up in French. Anyone know why?


Do you have Morefunc installed?
I think Date.diff is from that Add-in.


Age in Years

lookup help on YearFrac and try Datedif.

Caution: With a variety of dates, these functions are not accurate.

 

[Barry Katcher]

No, Dave. I have it loaded at home but not at the office. Do you have the URL? Appreciate.

 

[Dave Patton]

On 2003-01-02 15:38, Barry Katcher wrote:
No, Dave. I have it loaded at home but not at the office. Do you have the URL? Appreciate.

Date.diff is from Morefunc.

Datedif is a regular function in most versions of Excel. It was only documented in Excel 2000.

Barry Do you mean the UrL for Morefunc?

Download the morefunc add-in from

http://longre.free.fr/english/index.html

and install/activate the add-in via Tools|Add-Ins.