I've used the following formula to calculate a person's current age in years
based on a previously entered date of birth:

=ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)

However, if I haven't entered any data in the date of birth column, the
result cell shows the age "110", which throws out other calculations. Is
there any way to tell Excel not to enter any data if the date of birth is
blank?
Many thanks

Hi
Try this : =IF(P2="","",ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0))
HTH
John
"news.eternal-september.org" <(E-Mail Removed)> wrote in message
news:icjadq$vts$(E-Mail Removed)...
> I've used the following formula to calculate a person's current age in years
> based on a previously entered date of birth:
>
> =ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
>
> However, if I haven't entered any data in the date of birth column, the result
> cell shows the age "110", which throws out other calculations. Is there any
> way to tell Excel not to enter any data if the date of birth is blank?
> Many thanks

Please do not multipost.

=IF(P2="","",(ROUNDDOWN(YEARFRAC(P2,TODAY(),1),0)))

Your formula is OK for just years but if you wanted a bit more precision the
undocumented DATEDIF function might be used.

See Chip Pearson's site for more on that.

http://www.cpearson.com/excel/datedif.aspx


Gord Dibben MS Excel MVP


On Wed, 24 Nov 2010 15:19:22 -0000, "news.eternal-september.org"
<(E-Mail Removed)> wrote:

>I've used the following formula to calculate a person's current age in years
>based on a previously entered date of birth:
>
>=ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
>
>However, if I haven't entered any data in the date of birth column, the
>result cell shows the age "110", which throws out other calculations. Is
>there any way to tell Excel not to enter any data if the date of birth is
>blank?
>Many thanks

Hi Gord
Strange, I replied only in Ms Public.Excel and it got posted in 3 different
area.
Maybe something new??
Regards
John


"Gord Dibben" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Please do not multipost.
>
> =IF(P2="","",(ROUNDDOWN(YEARFRAC(P2,TODAY(),1),0)))
>
> Your formula is OK for just years but if you wanted a bit more precision the
> undocumented DATEDIF function might be used.
>
> See Chip Pearson's site for more on that.
>
> http://www.cpearson.com/excel/datedif.aspx
>
>
> Gord Dibben MS Excel MVP
>
>
> On Wed, 24 Nov 2010 15:19:22 -0000, "news.eternal-september.org"
> <(E-Mail Removed)> wrote:
>
>>I've used the following formula to calculate a person's current age in years
>>based on a previously entered date of birth:
>>
>>=ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
>>
>>However, if I haven't entered any data in the date of birth column, the
>>result cell shows the age "110", which throws out other calculations. Is
>>there any way to tell Excel not to enter any data if the date of birth is
>>blank?
>>Many thanks

Thank you, that's exactly what I needed/

As you may have guessed, I'm fairly new to this!

"Cimjet" wrote in message news:icjcci$k8u$(E-Mail Removed)...

Hi
Try this : =IF(P2="","",ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0))
HTH
John
"news.eternal-september.org" <(E-Mail Removed)> wrote in message
news:icjadq$vts$(E-Mail Removed)...
> I've used the following formula to calculate a person's current age in
> years based on a previously entered date of birth:
>
> =ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
>
> However, if I haven't entered any data in the date of birth column, the
> result cell shows the age "110", which throws out other calculations. Is
> there any way to tell Excel not to enter any data if the date of birth is
> blank?
> Many thanks

You're welcome, glad I could help.
John
"news.eternal-september.org" <(E-Mail Removed)> wrote in message
news:icje4m$65u$(E-Mail Removed)...
> Thank you, that's exactly what I needed/
>
> As you may have guessed, I'm fairly new to this!
>
> "Cimjet" wrote in message news:icjcci$k8u$(E-Mail Removed)...
>
> Hi
> Try this : =IF(P2="","",ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0))
> HTH
> John
> "news.eternal-september.org" <(E-Mail Removed)> wrote in message
> news:icjadq$vts$(E-Mail Removed)...
>> I've used the following formula to calculate a person's current age in years
>> based on a previously entered date of birth:
>>
>> =ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)
>>
>> However, if I haven't entered any data in the date of birth column, the
>> result cell shows the age "110", which throws out other calculations. Is
>> there any way to tell Excel not to enter any data if the date of birth is
>> blank?
>> Many thanks
>