Convert Date to Age

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been asked to create a report that allows for both age and/or
birthdate. Well if I have the age, the report works fine, however there are
some instances that I only have the birthdate, how would I convert that date
to age in the vba application and populate it into the age field.

Same text is

Column 2, row 2 is birthdate,
column 2, row 3 is age

I am assuming that an if...else loop would apply, but can't seem to get it
working.
 
try to use this function in vba

DateDiff("yyyy", Range("a1"), Now)

if a1 is before 1930 use all the four figures of the year in entering the
date in A1

see also help datediff in vba
 
jnlns,
Excel dates are just doubles. The whole number part is the number days since
1/1/1900 (assuming you have not changed to the 1904 system). So in B3:
=(NOW()-B2)/365
will give you the age. Format as you will.

Note that VBA (and the Office Spreadsheet Component) handle dates slightly
differently:

Debug.Print Format(1, "dd/mmmm/yyyy")
31/December/1899
And whilst this works in VBA, Excel cannot go beyond 1/1/1900.
Debug.Print Format(-1, "dd/mmmm/yyyy")
29/December/1899

As long as none of your people are into their second century, you should be
OK.

NickHK
 
Where A1 is the birthday, the cell with the formula returns the age.
=RIGHT(YEAR(TODAY()-A1),2)
 
Back
Top