Forum Discussion
Auto formatting
I was wondering if there was a way that on opening the spreadsheet it would highlight the DOB of those turning 12, 18 or 24 years of age within the next 2 months.
Any guidance or formula suggestions would be appreciated.
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.
11 Replies
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.
