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
--
Ian Sheppard

=IF(C1="","",IF(MONTH(TODAY())MONTH(C1),YEAR(TODA Y())-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"))
--
David Biddulph

"Ian Sheppard" wrote in message
...
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
--
Ian Sheppard

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"David Biddulph" wrote:

=IF(C1="","",IF(MONTH(TODAY())MONTH(C1),YEAR(TODA Y())-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"))
--
David Biddulph

"Ian Sheppard" wrote in message
...
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
--
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
--
Ian Sheppard


"Martin Fishlock" wrote:

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"David Biddulph" wrote:

=IF(C1="","",IF(MONTH(TODAY())MONTH(C1),YEAR(TODA Y())-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"))
--
David Biddulph

"Ian Sheppard" wrote in message
...
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
--
Ian Sheppard

Ian

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

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


Gord Dibben MS Excel MVP


On Sun, 7 Jan 2007 23:38:01 -0800, Ian Sheppard
wrote:

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