Conditional Formatting for Upcoming Dates

[cmangi]

I have a large table that contains a lot of due dates (column L), as we get to these due dates we enter transmittal numbers into a separate column (column B) . In the due date column (L) I would like the dates that are within 30 days highlight yellow and within 7 days highlight red. I would also like to have the formatting clear after transmittal numbers are entered.

Image if what my excel looks like: https://imgur.com/a/TqbE6

Any idea how I can make this work?

 

[oldbrewer]

=today()-[your cell ref]<8 color red

second condition =today()-[your cell ref]<31 color yellow

not sure what you mean with transmittal numbers do you want them white font so they are invisible ?

 

[cmangi]

I basically mean that we give that due date a number, meaning that action is complete. For example if Cell L45 is due in 7 or 30 days it should be highlighted, which indicates to my team that this task needs to be complete. Once the task is complete a number (we call it a transmittal number) is filled into cell B45 showing it is complete. I just want the formatting to clear after B45 is not blank.

Make more sense?

 

[oldbrewer]

=and(b45="",today()-[your cell ref]<8) color red

 

[cmangi]

Just a quick follow up question I think I am already there.

What do you mean by "your cell cell"?

 

[cmangi]

I feel like I am doing something wrong. Because when I try your first formula (ignoring the "and" function to start) it doesn't seem to calculate correctly. See the below link.

https://imgur.com/a/QhyjM

 

[oldbrewer]

by [your cell ref] I mean A1 or B2 wherever your cell is

my formula does not compute it means " today's date minus your date is less than 8" and if this is true turn the cell red

it is entered via conditional formatting