Calcing DOB

[Guest]

Hiya,

I have inherited a worksheet that returns 107 when DOB is empty. OK I accept
that 107 is unlikely but as we are all living that bit longer and I'm a bit
anal I would be real grateful if someone could point me in the right
direction to have the age cell blank if DOB is blank

Here's the formula

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

Ian

 

[David Biddulph]

=IF(C1="","",IF(MONTH(TODAY())>MONTH(C1),YEAR(TODAY())-YEAR(C1),IF(AND(MONTH(TODAY())=MONTH(C1),DAY(TODAY())>=DAY(C1)),YEAR(TODAY())-YEAR(C1),(YEAR(TODAY())-YEAR(C1))-1)))

It is shorter to use =IF(C1="","",DATEDIF(C1,TODAY(),"y"))

 

[Guest]

It may be easier to use the datedif function and test the cell c1 for
blankness.

=IF(ISBLANK(C1),"",DATEDIF(C1,TODAY(),"y"))

You could also check your leapyear year for and leaplings (if you have
them). The formula above assumes that they their birthday is occurs on March
1 if it is not a leap year.

Datedif is a lot easier to understand. For a detailed explanation see Chip
Pearson's site: http://www.cpearson.com/excel/datedif.htm

 

[Guest]

Thanks to both of you for your replies. Have to say I'm impressed by the sort
of guys who give uo their own time posting in these forums!

If I only need to know the age in whole years do i need to use the datedif
func?

Thanks again for answering so fast

 

[Gord Dibben]

Ian

Assuming DOB in A1................

=DATEDIF(A1,TODAY(),"y") & " years"


Gord Dibben MS Excel MVP