BravoBravoAu
Board Regular
- Joined
- Nov 8, 2011
- Messages
- 66
- Office Version
- 2016
- Platform
- Windows
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?!
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 |