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 wrote in message
...
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.

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


"jnlns" wrote in message
...
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.

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

=(NOW()-B2)/365 will give you the age.

No, I don't think he will be OK with that formula <g.

Let's say a woman was born on 10 November, 1948. Your formula says the age on
27 OCTOBER 2004 is 56.0. She won't be 56 for another 14 days.

Some years have 366 days. Hence the error when assuming only 365.

=DATEDIF(B2,TODAY(),"y") will give 55 with dates of 11/10/48 and 10/27/2004.


On Thu, 17 Feb 2005 15:46:34 +0800, "NickHK" wrote:

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


"jnlns" wrote in message
...
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.

To most people, age in years means the age attained on the most recent
birthday.

OTOH, your formula returns the age the person WILL ATTAIN on their birthday in
the current year. If the birthday hasn't occurred yet, your result is high by
1 year.

A person born on Nov 10, 1950 is 54 from 11/10/2004 through 11/9/2005, 56 from
11/10/2005 through 11/9/2006.

You can't get those results by simply comparing years. You also have to take
the month and day of the month into account, for both dates.


On Wed, 16 Feb 2005 23:46:50 -0800, Claud Balls wrote:


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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Did you try my formula, I tested it successfully. And I'm not comparing
years. I am subtracting and entire date (including month and day) from
todays date, then returning the resulting year.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!