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]

Are the years and months in separate columns or in the same column?

 

[CMW]

Are the years and months in separate columns or in the same column?

The years and months are in the same column, as they are a calculation based on the date of birth and the given date using this formula:

=DATEDIF(C43,$D$1,"Y")&" Years, "&DATEDIF(C43,$D$1,"YM")&" Months"

 

[Georgiboy]

Try the below CF formula, where C43 is the start of your range of data:

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

 

[Georgiboy]

Sorry your data (X Years, X Months) may be in column D, if it is swap, the C43 for D43

 

[CMW]

So there's no way of doing it with conditional formatting?

 

[CMW]

So there's no way of doing it with conditional formatting?

Sorry, just re-read your message! Will try.

 

[CMW]

Probably user error, bit your formula doesn't seem to work . This is what the spreadsheet looks like. What I would like is to be able to highlight those children that fall within certain age bands, and as I change the date at the top (each term), the formatting will change to reflect the fact that they may fall into a new age band. Hope that makes sense!

 

[Georgiboy]

Would be easier to see if you either show the column row headings in the image OR even better use the XL2BB addin to show the data.
XL2BB - Excel Range to BBCode

 

[CMW]

Sorry, can't get the XL2BB to work on my pc (weird set up!) Hope these images help ....