Auto formatting

[PPatel19]

I have a spreadsheet with DOB.
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.

[#2 · Accepted solution]

@PPatel19 

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.

[#3]

@Hans Vogelaar
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.

[#4]

@PPatel19 

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.

[#5]

@Hans Vogelaar
Thanks again. Explanation is also extremely useful.

[#6]

@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. 

[#7]

@PPatel19 

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.

[#8]

@Hans Vogelaar Hi again, another follow up. I’m working with an additional spreadsheet that has 5 columns with DOB.
Can the formula be amended so it can be applied to the different columns in one instant.
Thanks P

[#9]

@PPatel19 

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.

[#10]

@Hans Vogelaar So
=AND(AD2,AK2,AR2,AY2,BH2>EOMONTH(TODAY(),-24*12), AD2,AK2,AR2,AY2,BH2<=EOMONTH(TODAY(),-24*12+1))

[#11]

@PPatel19 

No, simply

=AND(AD2>EOMONTH(TODAY(),-24*12), AD2<=EOMONTH(TODAY(),-24*12+1))

[#12]

Ok thank you