=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

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(TODA Y())=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

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(E2 1),YEAR(TODAY())-YEAR(A1),IF(AND(MONTH(TODAY())=MONTH(E21),DAY(TODA Y())=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

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