Hi, I'm new to excel and I'm struggling with Conditional Formatting.
I have a spreadsheet that has names of peoples and the dates they attended a certain course - I've managed to get a formula which then calculates the date when their refresher course is due and that works fine.
What I wanted to do next was colour code the date that was generated as follows:
Date passed: Red
Date within the next 12 months: Yellow
Date more than 12 months away: Green
I created Conditional Formatting that I thought was working however if a date where the year is 2022 (so more than 12 months so should be green) is generated it is yellow rather than green?
I have the following:
RED: =AND($AT22<>"",$AT22<=TODAY())
YELLOW: =AND($AT22<>"",DATEDIF(TODAY(),$AT22,"Y")<=1,$AT22>TODAY())
GREEN: =AND($AT22<>"",DATEDIF(TODAY(),$AT22,"Y")>1)
Can anyone see what I have done wrong or why it's not working as expected please? Any help would be greatly appreciated
Thanks
Bookmarks