Excel Gurus - formula help please

[crabcake]

Before I take my laptop out to the range and shoot holes through it ... please ... someone help me if you can! This has literally given me a headache last night through this morning that I finally just managed to get rid of. Excel formulas have always kicked my ass.

I am creating an Excel spreadsheet with two date columns (start dates & end dates). What I want to do is - in each of these columns - create conditional formatting to make the dates show up in certain colors depending on whether they are past, present, future by certain parameters. Now I know, there is a Conditional Formatting option (Cond. Formatting --> "highlight cell rules" ...) to do this, except it only provides pre-defined timeframes that don't work for me, and it doesn't give me the flexibility to customize the date ranges that I want.

For example, what I want to is along these lines:

1) If the date shown in field = today, today + next 6 days, or today - last 5 days, have it turn that date bold yellow/orange (so it doesn't fade into white background).

2) If the date shown in field = 7 to 14 days out/ahead, have that date bold blue

3) If the date shown in field = 6 or more days passed, have that date bold red.

Anyone? :shrug:

 

[MR47930]

You must use the time now function "=NOW()" to give you a cell that keeps track of the current date/time, lets assume you put that in cell A1.
Then go to "conditional formatting" --> "new rule" --> "format only cells that contain"
Then go down to "edit the rule description"
Your first rule will be "Cell value" "between" "($A$1)-5" and "$A$1+7" and change the formatting to yellow.
2nd rule will be "Cell value" "between" "($A$1)+7" and "$A$1+14" and change the formatting to blue.
3rd rule will be "Cell value" "less than" "($A$1)-6" and change the formatting to red.

This will compare the date/time now with the "=NOW()" function to the dates that you want to see and highlight them the specific colors you want. Keep in mind that this is a simple formula and is strictly days, not workdays or anything like that. Hope it helps.

 

[crabcake]

You must use the time now function "=NOW()" to give you a cell that keeps track of the current date/time, lets assume you put that in cell A1.
Then go to "conditional formatting" --> "new rule" --> "format only cells that contain"
Then go down to "edit the rule description"
Your first rule will be "Cell value" "between" "($A$1)-5" and "$A$1+7" and change the formatting to yellow.
2nd rule will be "Cell value" "between" "($A$1)+7" and "$A$1+14" and change the formatting to blue.
3rd rule will be "Cell value" "less than" "($A$1)-6" and change the formatting to red.

This will compare the date/time now with the "=NOW()" function to the dates that you want to see and highlight them the specific colors you want. Keep in mind that this is a simple formula and is strictly days, not workdays or anything like that. Hope it helps.

Thank you, thank you, a million times ... THANK YOU!!!

 

[MR47930]

 

[crabcake]

Can I trouble you for one 'contingency' for this rule?

3rd rule will be "Cell value" "less than" "($A$1)-6" and change the formatting to red.

I don't need the rule to fire IF the % complete column (for that item) = 100%.

Is there an 'if/then' add on I can put on this?

 

[crabcake]

So, to clarify, Column F has the target dates for various activities. Column H has the %completed (displayed as - for example - 80%, 100%, etc.). What I want to do is either:

1) create a condition that does not change the value in the cells of Column F IF the value in the Column H cell is 100%; or,

2) create a condition (which I can order as first priority) where if the value in Column H is 100%, the value in Column F is turned bold purple, and then the remaining conditions do not fire.

Hope that helps to clarify, if anyone is able to help.

 

[crabcake]

Bumpity bump

 

[crabcake]

I got it!!!!



I feel like I just found the cure for liberalism!

 

[DoWhat]

I feel like I just found the cure for liberalism!

Good Job!!!!


I hate excel, because I am too stupid to figure out the formulas.