Forum Discussion
Auto formatting
Let's say the dates of birth are in D2:D100.
Select this range. D2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(AND($D2>EOMONTH(TODAY(),-288),$D2<=EOMONTH(TODAY(),-286)),AND($D2>EOMONTH(TODAY(),-216),$D2<=EOMONTH(TODAY(),-214)),AND($D2>EOMONTH(TODAY(),-144),$D2<=EOMONTH(TODAY(),-142)))
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Let's say the dates of birth are in D2:D100.
Select this range. D2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(AND($D2>EOMONTH(TODAY(),-288),$D2<=EOMONTH(TODAY(),-286)),AND($D2>EOMONTH(TODAY(),-216),$D2<=EOMONTH(TODAY(),-214)),AND($D2>EOMONTH(TODAY(),-144),$D2<=EOMONTH(TODAY(),-142)))
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
- HansVogelaar
Thank you very much. That worked perfectly
My understanding of formulas is fairly basic. If possible it would be great if you could provide a breakdown explanation of the formula.
So that I could shorten or widen the month range (e.g. 1 month or 3 months). Or even split the formula so I could have a different colour code for the different ages.I should have used EDATE instead of EOMONTH, sorry.
If you want different colors, create 3 rules, with formulas
AND($D2>EDATE(TODAY(),-24*12),$D2<=EDATE(TODAY(),-24*12+2))
AND($D2>EDATE(TODAY(),-18*12),$D2<=EDATE(TODAY(),-18*12+2))
AND($D2>EDATE(TODAY(),-12*12),$D2<=EDATE(TODAY(),-12*12+2))
EDATE(somedate, n) returns the date n months after somedate. If n is negative, it returns a date before somedate.
24 years is 24*12 months, 18 years is 18*12 months, and 12 years is 12*12 months.
So $D2>EDATE(TODAY(),-24*12) requires that the date of birth is later than 24 years ago.
And $D2<=EDATE(TODAY(),-12*12+2) requires that the date of birth is not later than 2 months after 24 years ago. If you want a narrower or wider range, change the number 2 accordingly. For example: for 3 months, you'd use
AND($D2>EDATE(TODAY(),-24*12),$D2<=EDATE(TODAY(),-24*12+3))
etc.
HansVogelaar - Couple of follow ups
1. You explained 'EDATE(somedate, n) returns the date n months after somedate. If n is negative, it returns a date before somedate.' - in terms of the formula you have provided would you just change the 'TODAY' to 'SOMEDATE'?
AND($D2>EDATE(SOMEDATE(),-24*12),$D2<=EDATE(TODAY(),-24*12+2))
2. Using the function 'EDATE' highlights the required age groups in this month July and August. Whereas the formula you provided with the function 'EOMONTH' highlighted August and September. I tried changing your formula as below so it would highlight as the latter, but that doesn't appear to work:
AND($D2>EOMONTH(TODAY(),-24*12),$D2<=EOMONTH(TODAY(),-24*12+2))
Apologies if I'm confusing matters.
To explain fully what I am trying to do. At the start of any given month I pull a report I then need to identify which individuals are turning 12, 18, 24 in the next month. So in August I would be looking at who would turn 12, 18, 24 in September. The option to widen that range is useful.
