Conditional Formatting based on age range

[CMW]

Hi
I have a list of names and dates of birth and have a separate column that calculates the age (at a given date) based on the date of birth, giving the answer in X Years, X Months.

I am trying to use conditional formatting to highlight the names that fall within a set age range for example all those who are between 8 years and 0 months and 9 years and 6 months at the given date. I thought I had cracked it, but when I looked at my results the conditional formatting was also highlighting cells where the age is 9 years, 11 months. Not sure where I have gone wrong or what the solution is (if there is one!).

TIA

 

[Georgiboy]

Select the range: D3:D13
Go to conditional formatting
Use the formula option
Input the below formula:

=LET( y,--TEXTBEFORE(D3," Y"), m,--TEXTBEFORE(TEXTAFTER(D3,", ")," M"), AND(y>=8,m>=0,y<=9,m<=6))

Set a format
Click OK

 

[CMW]

Dont know where I am going wrong, but it's not working!

 

[Georgiboy]

Looks like you are doing it right, I am using Excel online at the moment because I don't have access to my laptop but it shouldn't make a difference.

Are you doing this in Excel 365?

 

[felixstraube]

How about you count the total month and use this formula in your CF:

Libro1
	C	D	E
1		01/01/2024
2	Date of birth		Formula for CF
3	2013-06-14	10 Years, 6 Month	0
4	2013-06-14	10 Years, 6 Month	0
5	2012-06-17	11 Years, 6 Month	0
6	2014-11-01	9 Years, 2 Month	1
7	2012-11-11	11 Years, 1 Month	0
8	2014-05-22	9 Years, 7 Month	0
9	2014-05-30	9 Years, 7 Month	0
10	2015-03-09	8 Years, 9 Month	1
11	2015-01-22	8 Years, 11 Month	1
12	2015-03-15	8 Years, 9 Month	1
13	2013-09-27	10 Years, 3 Month	0
Hoja1

Cell Formulas
Range	Formula
D3:D13	D3	=DATEDIF(C3,$D$1,"Y")&" Years, "&DATEDIF(C3,$D$1,"YM")&" Month"
E3:E13	E3	=LET(m,ROUND(($D$1-C3)/30.4375,0), (m>=96)*(m<=114) )

Why divided by 30,4375 you ask? That number of days comes from dividing a century worth of days in 100 and then in 12. So you get the average number of days per month. And with this you can have a hundred years of date difference and the number of month calculated will be correct.

 

[felixstraube]

here is a working example:

Conditional formatting based on age.zip

You can delete the column E, it is only to show the formula used in the CF.

 

[CMW]

Hi - for some reason that formula didn't work either (just got a NAME error!). Not to worry, you put me onto another solution so I have found a work around.

Thanks for all you time and help though!

 

[Georgiboy]

Maybe you are using it on an older version of excel that does not support the functions we used?

 

[CMW]

Possibly! Thank you again for your time you helped me to find a very good work-around.

Merry Christmas!