Tags: age, birth, calculating, calculation, date, entries, entry, excel, exclude, formula, microsoft, msdn, software

Exclude blank data from formula calculation

On Microsoft » Microsoft Excel

2,648 words with 3 Comments; publish: Thu, 22 May 2008 05:52:00 GMT; (30693.75, « »)

I have a formula =YEAR($J$1)-YEAR(C2) calculating age from date of birth.

However some of my entries do not have an entry for date of birth, so their

'age' ends up as being (for example) 108.

Is there a way of making sure the formula does not calculate these ages?

Thanks

All Comments

Leave a comment...

  • 3 Comments
    • Donna,

      Your formula is incorrect for working out age and as an example put

      =Today() in J1

      31/12/2007 in C2

      Your formula will tell you that person is 1 yr old which clearly someone

      born on that day is only 4 months old.

      Use datedif instead and to get around the 108 problem check the cells are

      populated.

      =IF(AND(C2<>"",J1<>""),DATEDIF(C2,$J$1,"y"),"")

      Mike

      "Donna" wrote:

      > I have a formula =YEAR($J$1)-YEAR(C2) calculating age from date of birth.

      > However some of my entries do not have an entry for date of birth, so their

      > 'age' ends up as being (for example) 108.

      > Is there a way of making sure the formula does not calculate these ages?

      > Thanks

      #1; Thu, 22 May 2008 05:54:00 GMT
    • =IF(C2="","no record of birthdate",YEAR($J$1)-YEAR(C2))

      Gord Dibben MS Excel MVP

      On Mon, 14 Apr 2008 02:57:00 -0700, Donna <Donna.excel.questionfor.info.discussions.microsoft.com>

      wrote:

      >I have a formula =YEAR($J$1)-YEAR(C2) calculating age from date of birth.

      >However some of my entries do not have an entry for date of birth, so their

      >'age' ends up as being (for example) 108.

      >Is there a way of making sure the formula does not calculate these ages?

      >Thanks

      #2; Thu, 22 May 2008 05:55:00 GMT
    • But of course =YEAR($J$1)-YEAR(C2) doesn't calculate current age, it

      calculates the age attained during that calendar year. For someone born in

      December 2000, that formula would give their age as 8, whereas currently

      they are 7. DATEDIF(C2,$J$1,"y") might be better, & can be nested in a test

      for C2 not being blank as Gord suggested.

      --

      David Biddulph

      "Gord Dibben" <gorddibbATshawDOTca> wrote in message

      news:spg704lk7heaiu1bg1klfbusjej9nr3bip.excel.questionfor.info.4ax.com...

      > =IF(C2="","no record of birthdate",YEAR($J$1)-YEAR(C2))

      >

      > Gord Dibben MS Excel MVP

      > On Mon, 14 Apr 2008 02:57:00 -0700, Donna

      > <Donna.excel.questionfor.info.discussions.microsoft.com>

      > wrote:

      >>I have a formula =YEAR($J$1)-YEAR(C2) calculating age from date of birth.

      >>However some of my entries do not have an entry for date of birth, so

      >>their

      >>'age' ends up as being (for example) 108.

      >>Is there a way of making sure the formula does not calculate these ages?

      >>Thanks

      >

      #3; Thu, 22 May 2008 05:56:00 GMT