Cunditional formatting with greater than and weekday parameters

[Jeffrey021286]

I am trying to conditionally format a cell that contains a formula based on the day of the week that it currently is and the value that the formula is outputing. I want the cell to be highlighted if the curent day is Tuesday and the value is > 14.9 , Wednesday and the value is >29.9 , Thursday and >44.9 , Friday and >59.9 , and Saturday and 70.9. I have tryed to make a "if and" table with all the weekdays and just conditionally format the desired cell if it was > to cells in the table but found that when comparing the desired cell to a cell containing a formula it doesn't work

 

[StephenCrump]

Welcome to the Forum!

	A	B
1	Wed 17 Nov 2021	29.91
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=AND(WEEKDAY(A1)>2,B1>CHOOSE(WEEKDAY(A1,12),14.9,29.9,44.9,59.9,70.9,,))	text	NO

I can't help but wonder whether Saturday should be 74.9? If so, you could simplify to:

=AND(WEEKDAY(A1)>2,B1>15*WEEKDAY(A1)-30.1)

 

[Jeffrey021286]

T

Welcome to the Forum!

	A	B
1	Wed 17 Nov 2021	29.91
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=AND(WEEKDAY(A1)>2,B1>CHOOSE(WEEKDAY(A1,12),14.9,29.9,44.9,59.9,70.9,,))	text	NO

I can't help but wonder whether Saturday should be 74.9? If so, you could simplify to:

=AND(WEEKDAY(A1)>2,B1>15*WEEKDAY(A1)-30.

Welcome to the Forum!

	A	B
1	Wed 17 Nov 2021	29.91
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=AND(WEEKDAY(A1)>2,B1>CHOOSE(WEEKDAY(A1,12),14.9,29.9,44.9,59.9,70.9,,))	text	NO

I can't help but wonder whether Saturday should be 74.9? If so, you could simplify to:

=AND(WEEKDAY(A1)>2,B1>15*WEEKDAY(A1)-30.1)

Thanks that did the trick. I have another complex request if you have the time and desire to take a swing at it. Basically the numbers in the last formula where to track a production target throughout the week. The values where .1 less than the target so once I passed 14.9 I hit the target of 15 and so on. I also would like to have a cell with a formula that tells me how much I am currently short or past the dailey target thought out the week. Cell m5 adds several cells together and is what I applied your excellent formula to and I want k5 to return a value that shows m5 minus the daily targets of 15, 30, 45, 60, and 71. Any suggestions?

 

[StephenCrump]

I have another complex request .....

Can you please post some sample data and your required results, preferably using the XL2BB add-in: XL2BB - Excel Range to BBCode

 

[Jeffrey021286]

Can you please post some sample data and your required results, preferably using the XL2BB add-in: XL2BB - Excel Range to BBCodeI figured out a way to make it happen.

Can you please post some sample data and your required results, preferably using the XL2BB add-in: XL2BB - Excel Range to BBCode

I figured out a way to do it. It wasnt elegant but I made a table with a bunch of if function referencing a weekday cell to give the target values if true then had the cell I wanted to give the final value = the sum of the table - m5. Thanks again for all your help. I periodically try and do things that are pretty complex and sometimes outside my scope next time I end up stuck could I reach out to you directly you seem to be very fluent with this.

 

[StephenCrump]

I periodically try and do things that are pretty complex and sometimes outside my scope ....

A great way to learn!

... next time I end up stuck could I reach out to you directly ...

See Rule #4: Message Board Rules

I'm happy to help when I can, but by posting to the forum you access many potential helpers.