I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is their
some way to get around this problem.

Hmm.. that worked for me.

Here's another couple of approaches, see if they work for you.

=DATEDIF(D4,NOW(),"y")

which works with D4 as text or as a date.


or more similar to your original formula with D4 as text..

=YEAR(NOW())-YEAR(DATEVALUE(D4))



"Frank Malone" wrote in message
...
I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is their
some way to get around this problem.

Just to point out the difference in your two suggestions:

DATEDIF will give the age in years as of the 2nd date.

Subtraction will give the age the person will attain in the current year,
whether the birthday has occurred or not.

Example: A child was born on Mar 21, 2000, and the current date is Jan 12,
2005. Subtracting years gives 5. I expect most people would want a result of
4. DATEDIF will give 4.

On Wed, 12 Jan 2005 09:01:58 -0800, "Dave R." wrote:

Hmm.. that worked for me.

Here's another couple of approaches, see if they work for you.

=DATEDIF(D4,NOW(),"y")

which works with D4 as text or as a date.


or more similar to your original formula with D4 as text..

=YEAR(NOW())-YEAR(DATEVALUE(D4))



"Frank Malone" wrote in message
...
I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is their
some way to get around this problem.

I just found out if I enter 30 for 1930 all ok, if I enter 29 for 1929 get
error. But if I enter 1929 get correct age.

"Myrna Larson" wrote in message
...
Just to point out the difference in your two suggestions:

DATEDIF will give the age in years as of the 2nd date.

Subtraction will give the age the person will attain in the current year,
whether the birthday has occurred or not.

Example: A child was born on Mar 21, 2000, and the current date is Jan 12,
2005. Subtracting years gives 5. I expect most people would want a result
of
4. DATEDIF will give 4.

On Wed, 12 Jan 2005 09:01:58 -0800, "Dave R." wrote:

Hmm.. that worked for me.

Here's another couple of approaches, see if they work for you.

=DATEDIF(D4,NOW(),"y")

which works with D4 as text or as a date.


or more similar to your original formula with D4 as text..

=YEAR(NOW())-YEAR(DATEVALUE(D4))



"Frank Malone" wrote in message
.. .
I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is
their
some way to get around this problem.

That has to do with your Windows Regional settings, as to when a 2-digit year
is interpreted as 20th century and when 21st century.

On Wed, 12 Jan 2005 13:45:01 -0500, "Frank Malone"
wrote:

I just found out if I enter 30 for 1930 all ok, if I enter 29 for 1929 get
error. But if I enter 1929 get correct age.

"Myrna Larson" wrote in message
.. .
Just to point out the difference in your two suggestions:

DATEDIF will give the age in years as of the 2nd date.

Subtraction will give the age the person will attain in the current year,
whether the birthday has occurred or not.

Example: A child was born on Mar 21, 2000, and the current date is Jan 12,
2005. Subtracting years gives 5. I expect most people would want a result
of
4. DATEDIF will give 4.

On Wed, 12 Jan 2005 09:01:58 -0800, "Dave R." wrote:

Hmm.. that worked for me.

Here's another couple of approaches, see if they work for you.

=DATEDIF(D4,NOW(),"y")

which works with D4 as text or as a date.


or more similar to your original formula with D4 as text..

=YEAR(NOW())-YEAR(DATEVALUE(D4))



"Frank Malone" wrote in message
. ..
I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is
their
some way to get around this problem.

Myrna

So I thought also.

I played with mine and changed to 1940 - 2040.

Closed Excel, reopened and it still crapped out at any 2-digit year before
1930

Changed again to 1920 - 2020 and it continues to give #NUM error.

It is internal to Excel. 1930 seems to be the limit.

I seem to remember that Excel 97 came out with that limit.


Gord


On Wed, 12 Jan 2005 17:55:28 -0600, Myrna Larson
wrote:

That has to do with your Windows Regional settings, as to when a 2-digit year
is interpreted as 20th century and when 21st century.

On Wed, 12 Jan 2005 13:45:01 -0500, "Frank Malone"
wrote:

I just found out if I enter 30 for 1930 all ok, if I enter 29 for 1929 get
error. But if I enter 1929 get correct age.

"Myrna Larson" wrote in message
. ..
Just to point out the difference in your two suggestions:

DATEDIF will give the age in years as of the 2nd date.

Subtraction will give the age the person will attain in the current year,
whether the birthday has occurred or not.

Example: A child was born on Mar 21, 2000, and the current date is Jan 12,
2005. Subtracting years gives 5. I expect most people would want a result
of
4. DATEDIF will give 4.

On Wed, 12 Jan 2005 09:01:58 -0800, "Dave R." wrote:

Hmm.. that worked for me.

Here's another couple of approaches, see if they work for you.

=DATEDIF(D4,NOW(),"y")

which works with D4 as text or as a date.


or more similar to your original formula with D4 as text..

=YEAR(NOW())-YEAR(DATEVALUE(D4))



"Frank Malone" wrote in message
.. .
I'm using =(year(now()-datevalue(d4))-1900)
I enter DOB as text and excel finds age of person if born 1930 or after
1930. But if year of birth is 1929 or earlier get error #num! So is
their
some way to get around this problem.