Jul 27 2021 02:45 PM - edited Jul 27 2021 02:47 PM
Jul 27 2021 03:05 PM
SolutionLet'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.
Jul 28 2021 01:34 AM
Jul 28 2021 02:48 AM
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.
Jul 28 2021 11:13 AM
Jul 28 2021 02:17 PM
@Hans Vogelaar - 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.
Jul 28 2021 02:49 PM
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.
Aug 02 2021 11:15 AM
Aug 02 2021 11:18 AM - edited Aug 02 2021 11:18 AM
Remove the $ signs from the expression, i.e. D2 instead of $D2.
Make sure that the formula refers to the top left cell of the selection.
Aug 02 2021 11:46 AM
Aug 02 2021 12:46 PM
Jul 27 2021 03:05 PM
SolutionLet'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.