Formulas

[chedd via OfficeKB.com]

=IF(MONTH(TODAY())>MONTH(E21),YEAR(TODAY())-YEAR(A1),
IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODAY())>=DAY(E21)),
YEAR(TODAY())-YEAR(E21),(YEAR(TODAY())-YEAR(E21))-1))

The formula above works out the age of a person once i put their birth date
into another column attached to the formula. The only problem i am having is
I am requiring a zero value in the blank cells, but the blank cells keep
showing a fig of 106. Can anyone help in the above formula to show a zero
value until the birth date is asigned.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1

[Max]

Perhaps just add a simple error trap in front to check for the dependent
cells A1 and E21 being empty: =IF(OR(A1="",E21=""),0,your_formula)
viz.:
=IF(OR(A1="",E21=""),0,IF(MONTH(TODAY())>MONTH(E21),YEAR(TODAY())-YEAR(A1),IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODAY())>=DAY(E21)),YEAR(TODAY())-YEAR(E21),(YEAR(TODAY())-YEAR(E21))-1)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"chedd via OfficeKB.com" wrote:
> =IF(MONTH(TODAY())>MONTH(E21),YEAR(TODAY())-YEAR(A1),
> IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODAY())>=DAY(E21)),
> YEAR(TODAY())-YEAR(E21),(YEAR(TODAY())-YEAR(E21))-1))
>
> The formula above works out the age of a person once i put their birth date
> into another column attached to the formula. The only problem i am having is
> I am requiring a zero value in the blank cells, but the blank cells keep
> showing a fig of 106. Can anyone help in the above formula to show a zero
> value until the birth date is asigned.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200605/1
>

[chedd via OfficeKB.com]

Max wrote:
>Perhaps just add a simple error trap in front to check for the dependent
>cells A1 and E21 being empty: =IF(OR(A1="",E21=""),0,your_formula)
>viz.
>=IF(OR(A1="",E21=""),0,IF(MONTH(TODAY())>MONTH(E21),YEAR(TODAY())-YEAR(A1),IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODAY())>=DAY(E21)),YEAR(TODAY())-YEAR(E21),(YEAR(TODAY())-YEAR(E21))-1)))
>> =IF(MONTH(TODAY())>MONTH(E21),YEAR(TODAY())-YEAR(A1),
>> IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODAY())>=DAY(E21)),
>[quoted text clipped - 5 lines]
>> showing a fig of 106. Can anyone help in the above formula to show a zero
>> value until the birth date is asigned.


Thank you for you help in this and i have managed to sort out my report.

Again thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1

[Max]

Glad it helped !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"chedd via OfficeKB.com" wrote:
> Thank you for you help in this and i have managed to sort out my report.
> Again thank you