fill color if statement based on current field and another field

[ScablandsKing]

I have a column called YR1 Due which works:

If(
   DateDiff(Today(),ThisItem.'YR 1 DUE') <= 0,
   Color.Red,
   DateDiff(Today(),ThisItem.'YR 1 DUE') > 0 && DateDiff(Today(),ThisItem.'YR 1 DUE') <= 30,
   Color.Orange,
   DateDiff(Today(),ThisItem.'YR 1 DUE') > 30 && DateDiff(Today(),ThisItem.'YR 1 DUE') <= 60,
   Color.Yellow,
   DateDiff(Today(),ThisItem.'YR 1 DUE') > 90,
   Color.White

)

I need to add an if statement where if another column called YR1 Sat is complete the background will also be white. If YR1 Sat is completed I don't need to color code YR1 Due. Do I need a switch function or can I cascade if statements?

[Rajkumar_404]

Hi @ScablandsKing 

If(
ThisItem.'YR1 Sat' = "complete", // Replace "complete" with the actual value that represents completion
Color.White,
DateDiff(Today(), ThisItem.'YR 1 DUE') <= 0,
Color.Red,
DateDiff(Today(), ThisItem.'YR 1 DUE') > 0 && DateDiff(Today(), ThisItem.'YR 1 DUE') <= 30,
Color.Orange,
DateDiff(Today(), ThisItem.'YR 1 DUE') > 30 && DateDiff(Today(), ThisItem.'YR 1 DUE') <= 60,
Color.Yellow,
DateDiff(Today(), ThisItem.'YR 1 DUE') > 90,
Color.White
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[LaurensM]

Hi @ScablandsKing,

@Rajkumar_404's code will do the trick. To improve readability, shorten the If conditions and avoid code repetition, I would propose to start with the highest value and work your way down & use the With function. Depending on the column type of YR1 SAT, you may have to make a slight adjustment to the code.

I have provided inline comments to further explain the code:

With(
    {
        //Function scope variable to avoid code repetition
        wDateDiffDue: DateDiff(Today(), ThisItem.'YR 1 DUE')
    },
    If(
        //Adjust YR1 SAT section if necessary - currently we expect a text column with the value 'complete'
        wDateDiffDue > 90 || ThisItem.'YR1 Sat' = "complete",
        Color.White,
        //Between 90 & 31
        wDateDiffDue > 30,
        Color.Yellow,
        //Between 30 & 1
        wDateDiffDue > 0,
        Color.Orange,
        //0 or less
        Color.Red
    )
)

---

If this solves your question, would you be so kind as to accept it as a solution. ✔️

If you liked my solution, please give it a thumbs up. 👍

Connect with me: LinkedIn | Blog

[ScablandsKing]

Can't get it to work. The field has a data value of Yr1SATDataCardValue. I need to check if its blank or not. If its blank run the below color coding for Yr1 due. If Yr1SATDataCardValue has a value make the background white in due because its been completed. 

[ScablandsKing]

Yr1 Sat is a date column. Does that change things?

[ScablandsKing]

@LaurensM 

Got it simplified using your method

With(
    {
        wDateDiffDue: DateDiff(Today(), ThisItem.'YR 1 DUE')
    },
    If(
        wDateDiffDue > 90,
        Color.White,
        //Between 90 & 31
        wDateDiffDue > 30,
        Color.Yellow,
        //Between 30 & 1
        wDateDiffDue > 0,
        Color.Orange,
        //0 or less
        Color.Red
    )
)

Is there a way to add an is not blank function with the color.white section. Struggling with it being a date on not a "complete" text string. 

[LaurensM · Accepted solution]

Hi @ScablandsKing,

Would it be possible to adjust the first condition to the following:

//Previous value:  wDateDiffDue > 90
 wDateDiffDue > 90 || !IsBlank(ThisItem.'YR1 SAT') 

Here we assume that the item is 'complete' once YR1 SAT contains a value.

I hope this helps!

[ScablandsKing]

Perfect! Thank you!