Conditionally format expiration dates

[MsHotsauce]

Hello All,

I am trying to figure out how to get the Cells on my spreadsheet in column E or F to turn colors when they are 180, 90, 60, 30, and past due to their renewal date. Is there anyone that could possible help me with this. I am attaching a copy of the spreadsheet. Thank you very much in advance for your help with this. I have been working on it for 3 days and can't seem to figure it out.

Moderator note:
MsHotsauce please become familiar with our forum rules. Thread titles are rule #1. I changed the thread tile for you this time to something more descriptive.

[Melvosh]

The easiest way to do something like this is to use Conditional Formatting. If you haven't tried it, give it a shot. If you have, can you let us know what issues you've run into?

[63falcondude]

Hello and welcome to the forum.

Highlight F2:F77 > Conditional Formatting > New Rule > Use a formula
=$F2<0
Format: Fill color of your choice > OK > OK

...You haven't mentioned anything about what to do between 0 and 29 days until renewal date but that would go here...

Highlight F2:F77 > Conditional Formatting > New Rule > Use a formula
=$F2>=30
Format: Fill color of your choice > OK > OK

Highlight F2:F77 > Conditional Formatting > New Rule > Use a formula
=$F2>=60
Format: Fill color of your choice > OK > OK

Highlight F2:F77 > Conditional Formatting > New Rule > Use a formula
=$F2>=90
Format: Fill color of your choice > OK > OK

Highlight F2:F77 (or however far you need to extend the CF) > Conditional Formatting > New Rule > Use a formula
=$F2>=180
Format: Fill color of your choice > OK > OK

[FlameRetired]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Moderator note:
Since you are new to the forum as a courtesy I will change the title for this time.

[MsHotsauce]

My Apologies for the thread title. I will do better next time. I just was not sure conditional format was the only answer.

63falcondude Thank you very much for your help. It seems to be working however, I have a 1 question. With the expiration dates that are less than 30 days it seems to not have had any color change,
For cell 17 and 18 if I enter the formula =$F2>=29 it turns ever cell that is blank and cell 16,17 Red( that is the color I've chosen) How do i stop this from happening and only affect those cells?

[63falcondude]

With the expiration dates that are less than 30 days it seems to not have had any color change

From post #2:

...You haven't mentioned anything about what to do between 0 and 29 days until renewal date but that would go here...

If you wanted to include a rule for a number between 0 and 29 days, the formula would be =$F2>=0

The order of the Conditional Formatting rules matter. It is important to set the rules up in the order shown in post #2.
This will apply the rules in reverse order from the order that you created them.
To change the ordering of the rules, you can go into the Conditional Formatting Manage Rules window and use the up/down arrows.

[MsHotsauce]

I noticed you had pointed it out before sorry I did not address that, I am new to this.

Thank you very much for your help, your explanation makes perfect sense and explains why when I tried to do a formula for less than 30 days it was not working
correctly, it was out of order. Again, I really cannot thank you enough for taking the time our to help me with this.

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.