Highlight expired dates

[BravoBravoAu]

Hi all - I have read a number of previous posts about similar challenges but can't seem to resolve this.

I have a list of dates associated with training courses (Column A). Column B is the expiry date of the training - generally 12 months (eg =EDATE (A4, 12)).

I want to 'highlight' using fill and conditional formatting:
- in yellow dates in column B that are within one month of expiry, and
- in red dates in column B that are expired (eg today or beyond).

I think Column D is correct. I'm not sure how to conditional format Column B based on the true/false in Column C or D and whether they will conflict.

I have hundreds of rows of data!

Please help?!

	Column A	Column B	Column C	Column D
Row 1	Checking date:	=Today()
Row 2	Training Currency Date	Expiry Date	<1 month to expire	Expired
Row 3	01/02/23	=EDATE($A1, 12) 01/02/24 Highlighted red		=$B3<$B$1 True
Row 4	15/07/24	=EDATE($A2, 12) 15/04/25		=$B4<$B$1 False
Row 5	20/02/24	=EDATE($A3, 12) 20/02/25 Highlighted yellow		=$B5<$B$1 False

 

[SunnyAlv]

like this ?
create 2 conditional formatting in Column B

Formula RED:

=B3<$B$1

Formula Yellow:

=AND(B3-$B$1>0,B3-$B$1<30)

 

[BravoBravoAu]

Thank you @SunnyAlv - I definitely overcomplicated my attempt. Thank you for this solution.

 

[SunnyAlv]

Thank you @SunnyAlv - I definitely overcomplicated my attempt. Thank you for this solution.

no problem