Calcing DOB

I

Ian Sheppard

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
 
D

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

Martin Fishlock

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
 
I

Ian Sheppard

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
 
G

Gord Dibben

Ian

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

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


Gord Dibben MS Excel MVP
 
Top