conditional formatting cell colours based on deadlines and info in other cells

Question

conditional formatting cell colours based on deadlines and info in other cells

Rosa-Lotta Järvinen 0

Hi! I'm having a problem with conditional formatting. I have a work tracker that has a list of deadlines in cell I (starting I3, I'd be adding dates the more I get tasks), which I would like to highlight when the day comes closer:

green if the DL in column I is more than 6 workdays away
yellow if the DL is between 6-3 workdays away, and
red if the DL is less than 3 workdays away.

Additionally, in column J I'll be marking down when I have completed the DL, and hence would like the same row cell in column I to turn back to white if I've added a date on the J column so that it's easier to keep track. (so if the DL cell is red because the DL is still 2 days away, I'd like it to go white if I add the completion date in cell J. Same for all colours)

I've managed to create the colour codes, but when I try to add the condition of J column, it seems to mess with the whole system. Any tips?

Barry Schwarz 1,151 Reputation points

2023-10-30T10:39:31.85+00:00

Does the answer to a similar question in https://learn.microsoft.com/en-us/answers/questions/1400533/a-spreadsheet-where-the-cell-automatically-changes provide a solution?
Rosa-Lotta Järvinen 0 Reputation points

2023-10-30T11:44:19.9666667+00:00

Hi,

Not really. I'm quite new at creating the formulas, so I would hope specific formulas to use. The answer to the similar question didn't for example provide how to turn the DL cell back white, when date is added to the completion date column. Thanks for trying anyways!
Barry Schwarz 1,151 Reputation points

2023-10-30T17:59:39.4166667+00:00

Rules are applied in order as long as the "stop if true" box is not checked. The "manage rules" window has tools for moving rules up and down. The updates to your worksheet probably have a normal order of occurrence. Your rules should test for these condition in the same order.

For the completion issue you raised, you create a rule to set the white format you want when the completion column is not blank (or greater than 0 or whatever other test is appropriate) and make sure it is the last rule in the sequence.

On the other hand, if your conditions have a strict hierarchy (for example, if the completion date is set then no other data affects the format), then you can test each condition starting with the highest and set the "stop if true" box in each.

We won't do the work for you. San meet provided enough information to get you started. Create your formulas and when you have one that doesn't work like you want, ask a specific question.

Barry Schwarz 1,151 Reputation points

2023-10-30T10:39:31.85+00:00

Does the answer to a similar question in https://learn.microsoft.com/en-us/answers/questions/1400533/a-spreadsheet-where-the-cell-automatically-changes provide a solution?
Rosa-Lotta Järvinen 0 Reputation points

2023-10-30T11:44:19.9666667+00:00

Hi,

Not really. I'm quite new at creating the formulas, so I would hope specific formulas to use. The answer to the similar question didn't for example provide how to turn the DL cell back white, when date is added to the completion date column. Thanks for trying anyways!
Barry Schwarz 1,151 Reputation points

2023-10-30T17:59:39.4166667+00:00

Rules are applied in order as long as the "stop if true" box is not checked. The "manage rules" window has tools for moving rules up and down. The updates to your worksheet probably have a normal order of occurrence. Your rules should test for these condition in the same order.

For the completion issue you raised, you create a rule to set the white format you want when the completion column is not blank (or greater than 0 or whatever other test is appropriate) and make sure it is the last rule in the sequence.

On the other hand, if your conditions have a strict hierarchy (for example, if the completion date is set then no other data affects the format), then you can test each condition starting with the highest and set the "stop if true" box in each.

We won't do the work for you. San meet provided enough information to get you started. Create your formulas and when you have one that doesn't work like you want, ask a specific question.

conditional formatting cell colours based on deadlines and info in other cells

Activity