Conditional Formatting - Due Dates
Hello,
My objective is to apply conditional formatting to due dates in a connected sheet so my team knows which controls are due within the next two weeks. I am struggling to figure out if there is a way to set up a dynamic formula or if my only option is to set up separate conditions for each row, which is not ideal nor time effective.
Here is a small screenshot of my connected sheet:
Column L can be removed if there is a way to set up a dynamic formula using the data in column G. However, here is what I have set up for column M and would like to understand if this can be dynamically applied so I don't have to set this up for each row:
Below is the formula I had applied to column M previously, but I realized it didn't take into account the status of the control; which is important as I do not want due dates highlighted in red if they have already been sent for review / are completed.
Thank you in advance for your help!
-
I think you just need to reset the range where your conditional formatting applies to be the whole column. This is the box directly above the conditions where it currently says "M3", or where it says "M1" in your previous example of Upcoming / Past-Due formatting.
To apply to the whole column you can change the range to "M3:M", which applies it to all cells below M3. Or you can apply to a specific range using "M3:M#" where # is the row number where the conditional formatting should stop.
0
Thank you for the response, Matthew. I am aware I altered the range, but fixing it to apply to the entire column will not solve my issue. My question is whether the formulas used within conditional formatting can be dynamic because if I apply what I currently have to the full column, it will only take into account the value in cell L3 and apply it across all of the other controls which is not what I want.
0
The way you've written the formula should work. I've done the same thing to apply conditional formatting to the whole column, i.e., cell reference is $CR, where C is column letter, R is row number. e.g,
0
Thank you, Matthew. I've tried playing around with the formulas, but seem to be stuck and would appreciate your help. Do you know why the conditional formatting isn't working as i've designed it? The green and blue colors aren't being applied as i'd like them to be and i'm not sure if there's an issue with my formulas. It seems to all be based on cell G3, which isn't what I'd like to have happen. If there's an easier way to do any of this, please let me know. Otherwise, i'll come up with an alternative.
0
You don't need the =if(CONDITION,true,false) statements, you can simply use CONDITION if it evaluates true/false - e.g., anywhere your condition is an OR() or AND() you can use that on its own, you can also nest them, e.g., NOT(OR(a,b)) = anything but a or b
The Otherwise statement seems to act like IF false, then continue with rest of conditional formatting... but it's not always clear what's happening. Recommend not combining AND and OTHERWISE blocks - if you need to apply multiple conditions within one block write a single formula to do it, e.g., when formula is true fx=and($L3<$L$1,or($G3="Not Started",$G3=".....)) >> format red, then OTHERWISE, then next block,...
But I don't know why you're having the problem above with green formatting where it shouldn't be. It may be your OR statement for the green condition is too complicated. Try stripping the IF(OR(),T,F) statement down to force it to evaluate as false (e.g., just write FALSE where it says formula is true to force it) and see if it skips over to apply the next condition. If you can get it to work then, build your formula back up to where it is now step by step and see what breaks it. Should help pinpoint the problem anyway.
Could also try switching to make the green condition last in the chain so the Not Started ones come first...
0
I tried playing around with it more and unfortunately, it still wasn't working. I created an alternative that my team can use. Thank you for your time though in trying to help me troubleshoot this.
0サインインしてコメントを残してください。
コメント
6件のコメント