Forum Discussion
Conditional Formatting
The rule for blue has $N3>P$2. This should be $N3>=P$2.
You can simplify it to =AND($N3>=P$2,$N3<Q$2,P$2<TODAY()+5)
I think you need the following formula for the rule for red:
=AND($F3>0,P$2<=TODAY(),P$2>=$F3,TODAY()>$N2,ISBLANK(P3))
Hans,
Thank you, i made a little change so the red would not show up later than the blue and everything seemed to be working perfectly. Added a lot more data to test.... AND.. the formatting started getting wonky. See attached. At Row 41 the Red formatting stopped working..... unless I change the date in column N to a date that is prior to today. then it works in the next row. ( i have illustrated in screenshots 1 and 2 what happens). any ideas of what is going on?
think maybe i was rambling maybe this will help. In picture 5 I added two more rows, same date of start, same date of end. but you can see the formatting in the cells is not the same. one row does not have flesh (start date) and the other does not have red (missing data)
It might help if you explained in detail what each of the conditional formatting rules is supposed to do. I've just been trying to edit them blindly without any idea of what their purpose is.
- Thanks for being patient.... The yellow rules just defines the current week. The flesh colored defines the week the service starts and the blue defines the week the service ends. In each week of service, the employee is to hyperlink to a PDF report. The red rule notes which weeks do not have an "X" in them which indicates that a PDF has been linked
but with that formula, the red continues after a blue cell. the blue cell indicates the end of the service. see screenshots of conditional formatting when changing back to formula you gave me. what I added yesterday was working, but stopped after 40 rows.
The top 12 rows should have no color because their stop date was prior to 1/1/2021, all other rows should have no red to the right of a blue cell. that is why i added P$2<$N3
