Working out age of death using IF function

[JLeMasonry]

I am compiling a database featuring many different people. I need to be able to list their dates of birth, dates of death, and ages. So far, I have split these into three columns, E, F and G respectively, but I am having some trouble with the "Age" column. I need this column to do two things, if possible: if the person in question is alive, to list their current age at any given date, and if the person is dead, to list the age at which they died.

So far, my function for this column looks like this:

=IF([@DOD]>$B$1,DATEDIF([@DOB],$B$1,"y"),IF([@DOD]<$B$1,"Died"))

(B1 in this case is the specific date which will be changed accordingly.)

I feel like something needs to be added to the second IF function, to qualify that, if this function is true, the formula needs to work out the difference between DOD and DOB and display that as an age, but all my attempts to make this work have failed.

I appreciate that my formula is probably quite archaic and not altogether the most helpful formula in the world for this task, so any advice or suggestions would be appreciated!

 

[Dave Patton]

Excel 2010
	B	C	D
1	20-Jan-17
2
3	DOB	DOD	AGE
4	12-Dec-39	1-Feb-15	75
5	5-May-60		56
6
1e

Cell Formulas
Range	Formula
D4	=IF(ISNUMBER(Table1[@DOD]),DATEDIF(Table1[@DOB],MIN(B1,Table1[@DOD]),"y"),DATEDIF(Table1[@DOB],$B$1,"Y"))

Named Ranges
Name	Refers To	Cells
DOB	='1e'!$B$4:$B$5
DOD	='1e'!$C$4:$C$5
Num	={1,2,3,4,5,6,7,8,9,0}

N.B. The NUM named range does not apply to this question.

 

[snjpverma]

Try also works.

=IF(ISBLANK(F2),DATEDIF(E2,B2,"Y"),DATEDIF(E2,F2,"Y"))

 

[JLeMasonry]

Thank you so much, this has helped massively, and it's a much more finessed expression than the one I was previously dealing with. One more question, if I may: now that I am using the above formula pretty much word-for-word:

=IF(ISBLANK([@DOD]),DATEDIF([@DOB],$B$1,"Y"),DATEDIF([@DOB],[@DOD],"Y"))

Is there any way to have the Age column say "Died aged [Age at death]", rather than just a number? This is purely for aesthetic purposes, really, because the column now functions exactly as I need it to.

Thank you again!

 

[mole999]

it'll be something on the line of > ="Died aged [DATEDIF([@DOB],[@DOD]]" i.e test for the death and then use that in the formula

 

[snjpverma]

Try if this works,
=IF(ISBLANK([@DOD]),"Died at" & DATEDIF([@DOB],$B$1,"Y"),DATEDIF([@DOB],[@DOD],"Y"))

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	20-Jan-17	Key Date
2
3	DOB	DOD		Age
4	12-Dec-39	1-Feb-15	Died at age	75		Died at age 75
5	5-May-60		Key Date	56
6
7	T10_1701c	1e
1e

Cell Formulas
Range	Formula
G4	=IF(ISBLANK(C4),"",D4&" "&E4)
D4	=IF(ISNUMBER(C4),"Died at age",$C$1)
D5	=IF(ISNUMBER(C5),"Died at age",$C$1)
E4	=DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y")

Named Ranges
Name	Refers To	Cells
DOB	='1e'!$B$4:$B$5
Num	={1,2,3,4,5,6,7,8,9,0}

You could hide columns D and E and unhide them if you need the information in those columns.

 

[Dave Patton]

Excel 2010
	B	C	D
1	20-Jan-17	Key date age
2
3	DOB	DOD
4	12-Dec-39	1-Feb-15	Died at age 75
5	5-May-60		Key date age 56
6
1e

Cell Formulas
Range	Formula
D4	=IF(ISBLANK(C4),"Key date age"&" "&DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y"),"Died at age"&" "&DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y"))

Named Ranges
Name	Refers To	Cells
DOB	='1e'!$B$4:$B$5

Excel 2010
	B	C	D	E	F	G
1	20-Jan-17	Key date age
2
3	DOB	DOD		Age
4	12-Dec-39	1-Feb-15	Died at age	75	Died at age 75	Died at age 75
5	5-May-60		Key date age	56	Key date age 56	Key date age 56
6
1e

Cell Formulas
Range	Formula
D4	=IF(ISNUMBER(C4),"Died at age",$C$1)
D5	=IF(ISNUMBER(C5),"Died at age",$C$1)
E4	=DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y")
E5	=DATEDIF(DOB,MIN($B$1,IF(C5="",$B$1,C5)),"y")
F4	=IF(ISBLANK(C4),D4&" "&E4,D4&" "&E4)
F5	=IF(ISBLANK(C5),D5&" "&E5,D5&" "&E5)
G4	=IF(ISBLANK(C4),"Key date age"&" "&DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y"),"Died at age"&" "&DATEDIF(DOB,MIN($B$1,IF(C4="",$B$1,C4)),"y"))
G5	=IF(ISBLANK(C5),"Key date age"&" "&DATEDIF(DOB,MIN($B$1,IF(C5="",$B$1,C5)),"y"),"Died at age"&" "&DATEDIF(DOB,MIN($B$1,IF(C5="",$B$1,C5)),"y"))

Named Ranges
Name	Refers To	Cells
DOB	='1e'!$B$4:$B$5

If you will never have to do calculations on your results,
delete the range D4:F4.

 

[JLeMasonry]

Thank you everyone for your help. I have now amended the formula to read:

=IF(ISBLANK([@DOD]),DATEDIF([@DOB],$B$1,"Y"),"Died age"&" "&DATEDIF([@DOB],[@DOD],"Y"))

And this has fixed all the issues I had for this query. The formula will now return, if the person is dead, at what age they died (i.e., "Died age 76"), or, if they are alive, their current age.

Thank you all so much for your help!