Conditional Formatting

Carl P

New Member
Joined
Aug 22, 2018
Messages
3
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
=AND(A1-TODAY()>=8,A1-TODAY()<=14)
 

Carl P

New Member
Joined
Aug 22, 2018
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You forgot the parentheses after the first TODAY in your function:
Code:
[COLOR=#333333]=AND(D4-TODAY>=1,D4-TODAY()<=14)[/COLOR]
should be:
Code:
[COLOR=#333333]=AND(D4-TODAY[/COLOR][COLOR=#ff0000][B]()[/B][/COLOR][COLOR=#333333]>=1,D4-TODAY()<=14)[/COLOR]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top