Exclude blank data from formula calculation

D

Donna

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
 
M

Mike H

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
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
D

David Biddulph

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date of birth age formula 8
How do I calculate a person's age from date of birth and a date 2
Calculating When Somebody Reaches a Certain Age 4
Formula for Date Calculation 7
what formula do i need to calculate a persons age 12
Age calculation 5
How do you calculate age from just having a Date of Birth? 2
Calculating age from date of birth and ignoring if blank 5

Top