I use the following formula to change the background color of a cell according to the value of another cell on a different sheet. “=IF(TODAY()>$Report.B3+STYLE(“Good”),T(STYLE(“Bad”)))”
Want to use 3 different colors according to condition (orange as a state between good and bad. Is this possible?
I don’t think your formula works and not only bacuse of the problem of curly quotes Try to enclose in backticks.
=IF(TODAY()>$Report.B3+STYLE("Good"),T(STYLE("Bad")))
Usually I suggest to use conditional formatting, where you can have more then one condition (Switch the condition to “formula is”).
.
If you wish to do this inside one IF you could add a second condition in the alternative. I modified your formula to illustrate logic and my understanding of syntax - adapt to your needs
=IF(TODAY()>$Report.B3;
$Report.B3+STYLE("Good");
IF(TODAY()<$Report.B3;
$Report.B3+T(STYLE("Bad");
$Report.B3+T(STYLE("orange")
)
)
You could also use orange as primary background and change by condition, if there is no fourth case (like cell empty).
Thank you Wanderer, did not know about conditional formatting but your suggestion seems to be the right one. Just one formula problem:
Formula1: IF(TODAY()<$Academy.B3)
Formula2: IF(TODAY()>($Academy.B3+5))
Formula3: IF(TODAY() <=>($Academy.B3, $Academy.B3+5)
Formula 1 + 2 are working and changing from good to bad. Content of $Academy.B3 is a date. But Formula3 does not apply style “Warning”. It should show orange if the value is between B3 and B£+5 days. I guess there is something wrong with my formula.
Thanks for enlarging my knowledge!
<=> is not known to me and you need a second closing ) besides the problem of two arguments on the right side of the comparision
.
Try
IF(TODAY() <($Academy.B3+5) )
as the third condition will never tested, if the first was already met, you max include this here, without causing harm.
According to help <=> means between, >=< not between but am not an expert! Use this formula already in condition1 = is working but not as between two dates.
I’m quite sure there is no such thing in help because these “operators” simply don’t exist. Which help page did you get that from?
… but I don’t think Calc or Excel will accept C++ operators inside their tables. So I suggest to choose a fitting help-file.
(I can use HEUTE() for TODAY(), but I guess you would get an error.)
They are also not C++ operators.
OT: I’ve seen the first one, but didn’t check the second, C++20 is nothing I really used myself.
https://en.cppreference.com/w/cpp/language/operator_comparison
That C++20 three-way comparison operator though does not test if a value is between two other values.
Anyway, getting off-topic here…
Logic, I understand, but it is not working. Only switches between “good” and “bad”. The orange background displays if I use the formula =IF(TODAY()>$Academy.B3+STYLE(“Warning”),T(STYLE(“Bad”))). I thought maybe there is a problem with the style - but no.
Tried all and own ideas. Most formulas are able to change the background between two styles but all fail on a 3rd one. All three style can be displayed but not according to condition in one cell.
Please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead. Thanks.
It works! Thanks for all your information and encouragement. I used the conditional formatting as suggested by Wanderer early on. Then 3 conditions set the background of the cell to green, orange or red to alert the eye. The 3 formulas are:
IF(TODAY()>$Sheet8.$B$3) the date is less than today = green
AND((TODAY()>$Sheet8.$B$3);(TODAY()<($Sheet8.$B$3+5))) date between = orange
IF(TODAY()>($Sheet8.$B$3+5)) date more today +5 = red
Maybe not the most professional solution but then I am not the most computer literate!
1 Like