Nested IFs?

[Redwing2]

Hi,
I'm looking for a formula to return TRUE or FALSE if any of the 4 cells in a row verify someone is over the age of 55 based on their Date of Birth. The cells maybe blank which tends to return an error. I used IF(ISNUMBER) to test. Something like this =IF ISNUMBER(F2) IF TRUE then IF Today()-F2/365>55 --If any of the cells are TRUE return TRUE, IF False test the next cell, and so on.

Thanks...

 

[Redwing2]

Perhaps this?

20 04 13.xlsm
	A	B	C	D	E
1	DOB1	DOB2	DOB3	DOB4	Over 55
2	16-Apr-65		11-Apr-82	11-Apr-92	FALSE
3					FALSE
4	13-Apr-65	11-Apr-50		11-Apr-92	TRUE
Over 55

Cell Formulas
Range	Formula
E2:E4	E2	=IF(COUNT(A2:D2),MIN(A2:D2)<=EDATE(TODAY(),-660))

Wasn't quite sure if all cells can be blank like row 3 above and, if so, whether you want FALSE or "" returned.
If you want "" in that circumstance, use

=IF(COUNT(A2:D2),MIN(A2:D2)<=EDATE(TODAY(),-660),"")

HI Peter,

I tested the formula and I get incorrect replies

Book1
	A	B	C	D	E	F	G	H	I	J
1	First Name#1	DOB#1	First Name#2	DOB#2	First Name#3	DOB#3	First Name#4	DOB#4	Over 55?
2	John	October 29, 1976							FALSE
3	Alice	December 17, 1949	Tom	March 23, 1956					FALSE 70 64	<<should have been TRUE
4	Peter	February 17, 1955							TRUE 65
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=IF(COUNT(B2&D2&F2&H2),MIN(B2&D2&F2&H2)<=EDATE(TODAY(),-660),"")&" "&IF(ISNUMBER(B2),IF((TODAY()-B2)/365>55,INT((TODAY()-B2)/365),""),"") & " " & IF(ISNUMBER(D2),IF((TODAY()-D2)/365>55,INT((TODAY()-D2)/365),""),"") &" "&IF(ISNUMBER(F2),IF((TODAY()-F2)/365>55,INT((TODAY()-F2)/365),""),"") & " "& IF(ISNUMBER(H2),IF((TODAY()-H2)/365>55,INT((TODAY()-H2)/365),""),"")

 

[Redwing2]

Can you post a small sample of your data using the XL2BB button on the reply bar ?

Book1
	A	B	C	D	E	F	G	H	I	J
1	First Name#1	DOB#1	First Name#2	DOB#2	First Name#3	DOB#3	First Name#4	DOB#4	Over 55?
2	John	October 29, 1976							FALSE
3	Alice	December 17, 1949	Tom	March 23, 1956					FALSE 70 64	<<should have been TRUE
4	Peter	February 17, 1955							TRUE 65
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=IF(COUNT(B2&D2&F2&H2),MIN(B2&D2&F2&H2)<=EDATE(TODAY(),-660),"")&" "&IF(ISNUMBER(B2),IF((TODAY()-B2)/365>55,INT((TODAY()-B2)/365),""),"") & " " & IF(ISNUMBER(D2),IF((TODAY()-D2)/365>55,INT((TODAY()-D2)/365),""),"") &" "&IF(ISNUMBER(F2),IF((TODAY()-F2)/365>55,INT((TODAY()-F2)/365),""),"") & " "& IF(ISNUMBER(H2),IF((TODAY()-H2)/365>55,INT((TODAY()-H2)/365),""),"")

 

[KRice]

I didn't have any issues getting Peter's solution to work, at least with our initial assumption about the table structure, meaning four date cells appear in a continuous range. I think my solution will ignore the text and just operate on the dates. Try this in I2 and copy down:
=IFERROR(SMALL(A2:H2,1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY())),"")

 

[Redwing2]

I didn't have any issues getting Peter's solution to work, at least with our initial assumption about the table structure, meaning four date cells appear in a continuous range. I think my solution will ignore the text and just operate on the dates. Try this in I2 and copy down:
=IFERROR(SMALL(A2:H2,1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY())),"")

Thanks, KRice. It works. I had issues with this one as well at first but like Peters formula I changed A2:H2 to A2&D2&F2&H2 since the DOB fields in my actual spreadsheet are spread out much further. When I used the formula exactly how you laid it out it worked... thanks again!

 

[KRice]

You're welcome...I'm glad we arrived at something that works...maybe? I'm not sure about the concatenation of fields. If you are having issues with the cells between dates, try:
=IFERROR(SMALL((B2,D2,F2,H2),1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY())),"")

I got errors when I tried the version with ampersands...I'm assuming you meant B2 rather than A2 in your adjustment to the formula?

 

[Peter_SSs]

HI Peter,

I tested the formula and I get incorrect replies

Book1
	A	B	C	D	E	F	G	H	I	J
1	First Name#1	DOB#1	First Name#2	DOB#2	First Name#3	DOB#3	First Name#4	DOB#4	Over 55?
2	John	October 29, 1976							FALSE
3	Alice	December 17, 1949	Tom	March 23, 1956					FALSE 70 64	<<should have been TRUE
4	Peter	February 17, 1955							TRUE 65
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=IF(COUNT(B2&D2&F2&H2),MIN(B2&D2&F2&H2)<=EDATE(TODAY(),-660),"")&" "&IF(ISNUMBER(B2),IF((TODAY()-B2)/365>55,INT((TODAY()-B2)/365),""),"") & " " & IF(ISNUMBER(D2),IF((TODAY()-D2)/365>55,INT((TODAY()-D2)/365),""),"") &" "&IF(ISNUMBER(F2),IF((TODAY()-F2)/365>55,INT((TODAY()-F2)/365),""),"") & " "& IF(ISNUMBER(H2),IF((TODAY()-H2)/365>55,INT((TODAY()-H2)/365),""),"")

That isn't the formula that I posted. The one I posted (adjusted for the extra columns) is shown below. They are the expected results aren't they?

20 04 14.xlsm
	A	B	C	D	E	F	G	H	I
1	First Name#1	DOB#1	First Name#2	DOB#2	First Name#3	DOB#3	First Name#4	DOB#4	Over 55?
2	John	October 29, 1976							FALSE
3	Alice	December 17, 1949	Tom	March 23, 1956					TRUE
4	Peter	February 17, 1955							TRUE
Sheet2 (2)

Cell Formulas
Range	Formula
I2:I4	I2	=IF(COUNT(A2:H2),MIN(A2:H2)<=EDATE(TODAY(),-660))

 

[KRice]

I noticed that, Peter. Your recommendation works in my sheet, and it is the most efficient. Mine seems to work fine with the extra columns too...I'm not sure what the issue is.