Status column colour need to be change automatically

Rakesh Lavishetty
Rakesh Lavishetty ✭✭✭✭
edited 03/05/21 in Formulas and Functions

Here I have to apply formula for status coloumn,

If Actual hrs GREATER THAN Estimated hrs , i have to apply "Yellow" colour

EX : Actual hrs (10h) and Estimated Hrs (8h) = Yellow

if Actual hrs twice than that of Estimated hrs , i have to apply " Red" Colour

EX: Actual hrs (20h) and Estimated hrs (10h)= Red.

How do we accomplish this??

Thanks in advance.

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Rakesh,


    Try this:

    =if([actual hours]@row>=2*[estimated hours]@row, "Red",if([actual hours]@row>[estimated hours]@row,"Yellow","Green"))

    Because it tests the IF statements in order and stops once it reaches one statement that is true, you have to put the Red status first. That way, anything that has Actual hours 2x Estimated will be red first, then it will evaluate to see if the Actual is greater than Estimated; if not, it'll show green.


    Let me know if this works for you!


    Best,

    Heather

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Heather D We may run into an issue with the values being text. We may need to convert them to numbers before being able to compare.

    VALUE(SUBSTITUTE([Actual Hours]@row, "h", ""))

    VALUE(SUBSTITUTE([Estimated Hours]@row, "h", ""))


    =IF(VALUE(SUBSTITUTE([Actual Hours]@row, "h", "")) >= 2 * VALUE(SUBSTITUTE([Estimated Hours]@row, "h", "")), "Red", IF(VALUE(SUBSTITUTE([Actual Hours]@row, "h", "")) > VALUE(SUBSTITUTE([Estimated Hours]@row, "h", "")), "Yellow", "Green"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Rakesh Lavishetty You are going to want to add a helper column and put this formula in it:

    =VALUE(SUBSTITUTE([Estimated Hours]@row, "h", ""))


    Then you can sum this helper column.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!