Status column colour need to be change automatically
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

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

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

@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

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

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

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.

Please suggest me adding these hrs values in above coloumn
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????

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


Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!