Row colorization based on multiple date ranges

Chad, can you say in words what the rules are for coloring and I will try to help you.  List the conditions in the order that you want then evaluated.
for example

if this ......, then blue,
else if those other conditions ....., then green,
else if yet another test is true , then red .....

If calculated finish day is <= 3 days away and duration is <=10 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

If calculated finish day is <= 10 days away and duration is <=30 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

If calculated finish day is <= 30 days away and duration is <=90 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

If calculated finish day is <= 35 days away and duration is >=91 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

=We can try this, but it is not tested and its not easy to type perfect code with out the Syntax editor to help check for errors.

var number CFD = ToDays([Calculated Finish Date] - Today();
var number Dur = [Duration field in days];
var number PC = [Percent complete] *10

var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";
// laura has a great tool here https://laurahillier.quickbase.com/db/bhy8pumtp

IF(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10and $Dur < 30 and $PC < 85, $Yellow,

$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

$CFD < 35 and $Dur > 90 and  $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)

Thanks for the reply.

I made a couple of slight changes. But, still can't seem to get this to work. I'm getting a Formula Error

-- Incorrect variable type
The type of the variable does not match the return type of the expression.

I'm having trouble placing where this error is occuring



var number CFD = ToDate([Calculated Finish Date]) - Today();
var number Dur = [Duration];
var number PC = [% Complete] *10;

var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";

If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)

The first line will need to be what i posted

var number CFD = ToDays([Calculated Finish Date]) - Today();


Also what field type is [Duration]

[Duration] is a numeric field 

[Calculated Finish Date] is a  formula -work date 

I get an error with ToDays
Formula error -- Bad or missing arguments in function call

I'm guessing the [Calculated Finish Date] field is the bad guy here. It's been a thorn in side trying to figure this out

I had it wrong.  wrong placement of the brackets.

CFD = ToDays([Calculated Finish Date] - Today());

That was the spark I needed. Thanks! Still have some tweaking to do to get the colorization right though. Not showing the yellows or oranges...

var number CFD = ToDays(ToDate([Calculated Finish Date]) - Today());
var number Dur = [Duration];
var number PC = [% Complete] *10;

var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";

If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)

Got it! Grrr math

var number CFD = ToDays(ToDate([Calculated Finish Date]) - Today());
var number Dur = [Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start]) - Today());

var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";

If(
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

$SD < 1 and $CFD < 35 and $Dur >90 and $PC < 50, $Red,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)

For anyone still following this... I hope this is the finished product. Note I did change some field types...

var number CFD = ToDays([Assigned Finish Date] - Today());
var number Dur = [Calc Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start]) - Today());

var text Red = "#f75d59";
var text Yellow = "yellow";
var text Orange = "#fda302";

If(
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 50, $Red,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 70, $Orange,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 85, $Yellow,

$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 50, $Red,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 70, $Orange,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 85, $Yellow,

$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 50, $Red,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 70, $Orange,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 85, $Yellow,

$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 50, $Red,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 70, $Orange,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 85, $Yellow,

$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 50, $Red,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)

Very tidy and now easily maintainable if the rules need to change.

Thanks again for the help on this