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...

 

[Michael M]

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

 

[KRice]

Here is one idea to consider. Note that the logical checks whether any date of birth is less than today's date - 55 years...so it will return TRUE if anyone is over 55. If you want 55 or over, change the sign to <=

mrexcel_20200408.xlsm
	A	B	C	D	E
1	DOB1	DOB2	DOB3	DOB4	Age (y)
2	4/13/1965		4/11/1982	4/11/1992	FALSE
Sheet24

Cell Formulas
Range	Formula
E2	E2	=SMALL($A$2:$D$2,1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY()))

 

[Peter_SSs]

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),"")

 

[mole999]

@Redwing2

Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

 

[Redwing2]

Here is one idea to consider. Note that the logical checks whether any date of birth is less than today's date - 55 years...so it will return TRUE if anyone is over 55. If you want 55 or over, change the sign to <=

mrexcel_20200408.xlsm
	A	B	C	D	E
1	DOB1	DOB2	DOB3	DOB4	Age (y)
2	4/13/1965		4/11/1982	4/11/1992	FALSE
Sheet24

Cell Formulas
Range	Formula
E2	E2	=SMALL($A$2:$D$2,1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY()))

Thanks, KRice. This is closer then I got but if all the cells are blank (a few of them are) then it returns #VALUE!

 

[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, thanks, Peter. This one works!!

 

[Redwing2]

@Redwing2

Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.

Thanks. I posted on another site in case I did not receive any replies but members of this site have been very helpful!

 

[KRice]

Yes, if all cells are blank, my earlier approach will return an error. The easy fix for that is to wrap the entire command in an IFERROR like this:
=IFERROR(SMALL(A3:D3,1)<DATE(YEAR(TODAY())-55,MONTH(TODAY()),DAY(TODAY())),"")

If you want nothing returned in the case of all empty cells, then leave the formula shown here, and if you want some other message displayed, just insert that message between the last set of double quotes.

 

[Redwing2]

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

HI Michael,

Thanks for letting me know about the XL2BB button. I will do this next time.