Forum Discussion
Conditional Formating
I have a spread sheet that tracks Mowing times for our crews. One column states how much time it is supposed to take for each customer. Then other columns have the number of guys on the crew, start time and stop time. The another would calculate how much time that it took. It is that column that i want to use the conditional formatting on. I want it to turn yellow if the time it took was more than the time it should have taken. I Highlighted the whole column and used the 'use a formula to determine which cells to format' and typed for the rule,"=IF(Q3="","",Q3>K3)." Then I formatted it to highlight yellow if true. It works on most of the cells but randomly (It seems) there are some, when the time it took is equal to the time it should have taken but it highlights it anyway.
Any tips or help would be great! So it will only highlight the ones that are over the time it should take.
Hi,
First, you may join all you rules into one. In file I joined two with rule
=(L3>$K3)*(L3<>"")
applying it to
=$L$3:$L$323,$AF$3:$AF$323
Second, your time is only looks the same. If you copy AF92 and AK92 (both 1:24) and paste as values it'll be 0.0583333333333333 and 0.0583333333333335.
You may round the cells formatted as time to 1 second as
=MROUND(<cell>,"0:00:01")
See for AF and AK columns
7 Replies
Hi,
First, you may join all you rules into one. In file I joined two with rule
=(L3>$K3)*(L3<>"")
applying it to
=$L$3:$L$323,$AF$3:$AF$323
Second, your time is only looks the same. If you copy AF92 and AK92 (both 1:24) and paste as values it'll be 0.0583333333333333 and 0.0583333333333335.
You may round the cells formatted as time to 1 second as
=MROUND(<cell>,"0:00:01")
See for AF and AK columns
When I tried your formula
=(L3>$K3)*(L3<>"")
It made cells that had nothing in them highlighted....would that because I didn't erase the old rule?
So I can make 1 rule to work for all the cells on the whole sheet? I don't have to do a different rule for each column?
Thanks! the MROUND part worked!
In attached file I combined all rules in one. From my point of view when you add new column to highlight it's easier to add another region to the rule rather than generate new rule for each new region.
However, if you are comfortable with your existing rules - keep them, they also work.
Those cells that you said i could round, already have a formula in it to find the time it took. So are you meaning to make it round? By making another conditional format rule? or how would I make that cell round as well as calculate?
Please check the formula within the file I attached previous time. In column AF
=IF((([@Stop4]-[@Start4])*[@[How many guys4]]>0),MROUND(([@Stop4]-[@Start4])*[@[How many guys4]],"0:00:01"),"")
As for conditional formatting changes are optional.
- correct rule formula shall return TRUE or FALSE (or equivalent 1 and 0, etc.). In your case for some cells it returns "" which causes error in rule and it won't work for that cell. Actually result is the same as for FALSE but better to use more correct formulas;
- better to follow "one colour - one rule", all your rules could be combined in one, but that's optional
- Hello,
Not here for the answer, just wanted to point out that no files with personal info should be uploaded since this is clearly an infringement of data protection.
You don’t just have names and contact info there, but gate codes and where house keys are hidden..
Not to mention comments on clients.
You should take it down and just upload a sample with bogus data.
Best!
