May 07 2019 08:59 PM
May 07 2019 11:37 PM
Whoa, you may want to learn how to use the Snipping tool that is built into your computer, so you can take screenshots with software instead of using a camera to photograph what's on your monitor.
You can also attach a sample file to a post here. Use the "Choose Files" button below the reply box to attach a file with your post.
Now to your question:
It seems that you have two questions. One is about conditional formatting, and the other one is about how to show the date only in rows that have a real date in column C.
How to show next refresher due only for rows with a date in column C: Start in D4 with this formula and copy down.
=IF(C4>0,DATE(YEAR(C4)+1,MONTH(C4),DAY(C4)),"")
How to use conditional formatting to highlight cells:
Select cell D4 down to the bottom of your list. Then click Conditional formatting > New Rule > Use a formula to determine ... > and enter this formula into the formula box:
=AND(ISNUMBER($C4),$C4>TODAY())
use green for that.
Then add another rule with this formula:
=AND(ISNUMBER($C4),$C4<TODAY())
Use red for that.
I'm not sure if that is really what you want to show, but this does what you described. If the date in C is less than today, D will be red. If the date in C is greater than today, D will be green. If there is no date in C, D will not change colour.
It does not really make sense that the last refresher training in C would be a date that is greater than today, but that is what you asked for.
The screenshot shows the formula for column D in the formula bar and the rule for the green conditional format following your requirements (which don't make sense), because the last refresher training cannot be in the future. Or can it?
I'm attaching the file so you can have a play with it .
May 07 2019 11:58 PM
May 08 2019 12:14 AM - edited May 08 2019 01:32 AM
@Ingeborg Hawighorst Sorry looks like something funny is going on.
anything before the current day needs to be Red and anything that is in the future needs to be Green in the Next refresher training cell
Also if you were to input the current day into the last refresher training it fills in the next refresher with a clear box, how could I go about having that as green aswell?