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.

 

[Guest]

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)))

 

[chedd via OfficeKB.com]

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

 

[Guest]

Glad it helped !
Thanks for the feedback ..