Formula to show 2 months within 3rd birthday

[Mally]

Hi.

In cell A1 I have a date of birth.

In cell B1 I want to display a cell colour of red if todays date is within 2
months of the 3rd birthday.

I'm going to copy this formula down.

Thanks in advance for any help.

 

[David Biddulph]

CF/ Formula Is:
=AND(DATE(YEAR(A1)+3,MONTH(A1)-2,DAY(A1))<TODAY(),DATE(YEAR(A1)+3,MONTH(A1)+2,DAY(A1))>TODAY())
You may want to change < to <= and > to >=, depending on whether you want
the limits to be inclusive or exclusive.
And you may also wish to change the formula if you only want to cover dates
within 2 months BEFORE the 3rd birthday and to exclude dates AFTER the 3rd
birthday.

 

[Jacob Skaria]

1. Select the cell/Range Column B (B1 is the active cell)
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula

=AND(DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))<=TODAY(),DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))>DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())))

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes

 

[Mally]

Hi David

Thanks for your help. This worked ok.

CF/ Formula Is:
=AND(DATE(YEAR(A1)+3,MONTH(A1)-2,DAY(A1))<TODAY(),DATE(YEAR(A1)+3,MONTH(A1)+2,DAY(A1))>TODAY())
You may want to change < to <= and > to >=, depending on whether you want
the limits to be inclusive or exclusive.
And you may also wish to change the formula if you only want to cover dates
within 2 months BEFORE the 3rd birthday and to exclude dates AFTER the 3rd
birthday.

 

[Mally]

Hi Jacob

I tried your formula but it didn't work. Maybe i was doing something
incorrect. Anyway David's post above worked so thank you for your help.