Status column colour need to be change automatically

Rakesh Lavishetty

colour.jpg

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.

Heather Duff

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

@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

@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.

Heather Duff

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

@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"))

Heather Duff

@Paul Newcome I wondered about that! When I've used a column set as a duration, it calculated them as values, so I guess I made that assumption. If it's not set as a duration, my formula definitely wouldn't work!

@Rakesh Lavishetty, will you please let us know which ends up working for you?

Rakesh Lavishetty

Hi @Heather D and @Paul Newcome ,

Hope you are doing good!!!!

Thank you for your time writing in me.

In my case both formulas are working fine but @Paul Newcome suggested formula more accurate, so i m using it in my sheet.

Thanks.

Rakesh Lavishetty

Please suggest me adding these hrs values in above coloumn

colour.jpg

with help of SUM formulas am unable to add total values with "h". In screenshot, in place of arrow mark i should get 75h. please suggest how to accomplish this????

Paul Newcome

@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.

Rakesh Lavishetty

Hi @Paul Newcome

In my case, suggested formula perfectly working, Thanks.

Regards,

Rakesh.

Paul Newcome

Happy to help. 👍️