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.
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.
SOMEDATE was only meant to explain the syntax of EDATE (and EOMONTH); it can be replaced with any date.
In your particular situation, we use TODAY() because we want to refer to the current date.
If you want to highlight dates in the next calendar month, we'll use EOMONTH:
=AND($D2>EOMONTH(TODAY(),-24*12),$D2<=EOMONTH(TODAY(),-24*12+1))
=AND($D2>EOMONTH(TODAY(),-18*12),$D2<=EOMONTH(TODAY(),-18*12+1))
=AND($D2>EOMONTH(TODAY(),-12*12),$D2<=EOMONTH(TODAY(),-12*12+1))
For example EOMONTH(TODAY(),-24*12) is the last day of the month 24 years before today. So currently, that is 31-July-1997.
And EOMONTH(TODAY(),-24*12+1) is the last day of the month after that. So currently, that is 31-August-1997
=AND($D2>EOMONTH(TODAY(),-24*12),$D2<=EOMONTH(TODAY(),-24*12+1)) says: the date of birth is after 31-July-1997 and on or before 31-August-1997.
Next month, when it is August 2021, EOMONTH(TODAY(),-24*12) will be 31-August-1997 and EOMONTH(TODAY(),-24*12+1) will be 30-September-1997.
If you would like to highlight those that will be 24 in the next 2 calendar months, use
=AND($D2>EOMONTH(TODAY(),-24*12),$D2<=EOMONTH(TODAY(),-24*12+2))
etc.
- HansVogelaar
Thanks again. Explanation is also extremely useful.