Datevalue if born before 1930

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" <[email protected]> wrote in message
news:euvbEQM%[email protected]...
> 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." <[email protected]> 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" <[email protected]> wrote in message
>news:euvbEQM%[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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." <[email protected]> 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" <[email protected]> wrote in message
>>news:euvbEQM%[email protected]...
>>> 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" <[email protected]>
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" <[email protected]> wrote in message
>news:[email protected]...
>> 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." <[email protected]> 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" <[email protected]> wrote in message
>>>news:euvbEQM%[email protected]...
>>>> 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
<[email protected]> 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" <[email protected]>
>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" <[email protected]> wrote in message
>>news:[email protected]...
>>> 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." <[email protected]> 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" <[email protected]> wrote in message
>>>>news:euvbEQM%[email protected]...
>>>>> 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.
>>>>>
>>>>>
>>>>
>>>
>>

From Excel 97 Help.....

Note When you enter a date in Microsoft Excel 97 and you enter only two
digits for the year, Microsoft Excel enters the year as follows:

· The years 2000 through 2029 if you type 00 through 29 for the year.
For example, if you type 5/28/19, Microsoft Excel assumes the date is May 28,
2019.
· The years 1930 through 1999 if you type 30 through 99 for the year.
For example, if you type 5/28/91, Microsoft Excel assumes the date is May 28,
1991.


Gord

On Wed, 12 Jan 2005 18:04:27 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>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
><[email protected]> 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" <[email protected]>
>>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" <[email protected]> wrote in message
>>>news:[email protected]...
>>>> 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." <[email protected]> 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" <[email protected]> wrote in message
>>>>>news:euvbEQM%[email protected]...
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>
>>>>
>>>