Conditional Formatting - change color based on how far/close date is from today

Hello,

I need help with conditional formatting. I have an excel worksheet where I track orders that have been placed and any information regarding these orders, e.g., date it was submitted [Column A], Estimated Ship Date (ESD) [F], Ship date [G], Tracking [H], Estimated Time of Arrival (ETA) [I], etc. To help me prompt with what I need to do next, as there are a lot of orders to keep track of, I would like these cells to change color as these dates approach/have past.

1) I would like the cell in the ESD column (F) to change color if it is 5 days past the date it was submitted (5 days past date in column A). Then I would like it to change back if the cell in the ESD column is no longer blank.

2) I would like the cell in the Ship Date column (G) to change color if today is after the date in the ESD column (F). Then I would like it to change back if the cell in the Ship Date column is no longer blank.

3) I would like the ETA column (I) to color scale - darker if the date in this column is closer to today's date and lighter the further it is from today's date. Then I would like it change back if the cell in this column is no longer blank.

Please help!

Thank you

Can you post a small sample file showing expected outcomes. This allows respondents to test any solutions that are offered. Thank you.

Originally Posted by JohnTopley

Can you post a small sample file showing expected outcomes. This allows respondents to test any solutions that are offered. Thank you.

Hello,

I have attached a sample chart for your reference. Thank you so much!

Originally Posted by JohnTopley

For 1).

=AND(TODAY()+5>$A3,$F3=0)

for 2),

=AND(G3="",F3<>"",TODAY()>F3)

3)

What date do we compare against ?

Hello John,

Thank you for your response - I will try the first two right now. For number 3), I'm not sure I understand your question. Let me revise what I need for number 3: 3) I would like the ETA column (I) to color scale - darker if the date in this column is closer to today's date and lighter the further it is from today's date.

Let me know if you need more information.

Originally Posted by JohnTopley

For 1).

=AND(TODAY()+5>$A3,$F3=0)

for 2),

=AND(G3="",F3<>"",TODAY()>F3)

3)

What date do we compare against ?

Also,

I tried the two formulas you gave me. The one for #2 works fine - but the one for #1 highlights the cell even when it is less than 5 days past the day - please help!

THANK YOU SO MUCH!!!!

For 3

=AND(I3<>"",I3>TODAY()-5) lighter

=AND(I3<>"",I3>TODAY()-3) darker

Originally Posted by FDibbins

Did you see my comment in post #3?

Hello,

Thank you for your response. I apologize for the confusion; let me rephrase my request: I would like the ETA column (I) to color scale, with warmer/darker colors for the dates in this column closest to TODAY's date (into the future), and then lighter colors for the cells in this column with dates that are furthest from TODAY's date (into the future). Once the "Complete Date" column (L) is no longer blank, then I no longer want Column (I) to color scale - so go back to the original color. I have attached an example chart for your reference.

For clarity:

If the Complete Date column (L) is not blank, then I want the ETA column (I) to color scale - with darker colors for dates that are approaching, ligher colors for dates that are further away. The darker colors will be an indication that we are expecting a delivery soon, and the lighter colors will indicate that we won't be expecting that for a few weeks/months from now. I just want these cells to change color to trigger me to be on the look out for these orders. If the order is complete (column L <>""), then I no longer need it to change color because I do not need a trigger for that order. I hope this helps.

Please let me know if you have any questions.

Thank you!!!!!!!

For 1

=AND(TODAY()>$A3+5,$F3="")

Originally Posted by JohnTopley

For 1

=AND(TODAY()>$A3+5,$F3="")

This one worked - thank you SO MUCH!! I truly appreciate your help.

Originally Posted by JohnTopley

For 3

=AND(I3<>"",I3>TODAY()-5) lighter

=AND(I3<>"",I3>TODAY()-3) darker

Hello John,

For this one, do I just enter this as a new rule "Use a formula to determine which cells to format" for the rule type? And do I enter this rule in Column I? Because it didn't work.

Also, is there a way to get these to color scale with gradient colors?