Conditional Formatting

[Carl P]

Ladies and Gentlemen,

I have just taken 2 excel courses and I was unable to figure out how to do this based on the knowledge I received. I work for a construction company and I am trying to make life easier for us all. I have a table with 3 columns.

Column 1 - "Due Date" (This is the date in which the specific product needs to arrive on the job site, I manually enter this)

Column 2 - "Lead Time" (This is the specific amount of days / weeks it will take for the vendor to manufacture and send to the job site, I manually enter this)

Column 3 - "Release NLT" (It was fairly easy to create this number with a formula of "column 1 - column 2" to create the date that we absolutely must release the product to be sent to the site. The tricky part is this, I want the Release NLT column 3 to become red 2 weeks out and for it to become yellow 4 weeks out from today's date.)

I tried to do conditional formatting with "Next week" but, it would ignore the rest of the days in THIS week. Any and all assistance would be greatly appreciated.

Respectfully,
Carl P

 

[Joe4]

Welcome to the Board!

The key thing to understand with dates is how Excel sees them. It stores them as numbers, specifically the number of days since 1/0/1900.
So, in order to get the number of days between two dates, simply subtract them.
Also, you can use TODAY() to return the current date in Excel.

So, armed with that information, you should be able to build a Conditional Formatting formula to do what you want.
For example, to build a formula to check to see if something is 8 to 14 days in the future, the formula would look something like this (for a date entry in cell A1):

=AND(A1-TODAY()>=8,A1-TODAY()<=14)

 

[Carl P]

Joe,

Thanks for the response. I tried to enter in the data as you explained and I am still having issues with it. Based on what you said, I should be creating a new rule under conditional formatting, no? I chose "Use a formula to determine which cells to format". I plugged in your formula (with my cells) and chose for the cells to become red if they meet the criteria. However, nothing is becoming red (before or after today's date). I used =AND(D4-TODAY>=1,D4-TODAY()<=14)

Any ideas? I'm sure it is purely operator error. The cells I am adding the rules for, already have an internal formula for b4-c4 to get d4s date.

Respectfully,
Carl

 

[Joe4]

You forgot the parentheses after the first TODAY in your function:

[COLOR=#333333]=AND(D4-TODAY>=1,D4-TODAY()<=14)[/COLOR]

should be:

[COLOR=#333333]=AND(D4-TODAY[/COLOR][COLOR=#ff0000][B]()[/B][/COLOR][COLOR=#333333]>=1,D4-TODAY()<=14)[/COLOR]

 

[Carl P]

Joe,

You are the greatest! Thank you so much.

Respectfully,
Carl P

 

[Joe4]

You are welcome.
Glad I was able to help!