Conditional formatting in table

[Luwak]

Hi,

I have a table where I would like to add lines of data for time spent on certain categories of activities each day. The columns are date, hours and category. I would like to give all lines where the total for that date is not exactly 24 (either less or more) a different color. What formula should I use for this conditional formatting?

 

[sureshsonti]

Hi,

I have a table where I would like to add lines of data for time spent on certain categories of activities each day. The columns are date, hours and category. I would like to give all lines where the total for that date is not exactly 24 (either less or more) a different color. What formula should I use for this conditional formatting?

Is this want you are looking for

Under New Formatting Rule
Select Format only cells that contain

 

[Luwak]

Not exactly, one day can have several lines for different activities, but I want the data to be highlighted when the sum of hours for that day in the whole table is not 24. In the attachment I formatted manually, 1 November and 31 October each have 24 hours reported so that is fine. 25 and 26 October and 2 November have too many hours reported. 31 October is not fully reported (yet).

 

[pecoflyer]

Following formula can be used as CF
=SUMPRODUCT($A$2:$A$9=A2,INT($D$2:$D$9)+MOD($D$2:$D$9,1)*100/60)<>24

 

[Luwak]

Following formula can be used as CF
=SUMPRODUCT($A$2:$A$9=A2,INT($D$2:$D$9)+MOD($D$2:$D$9,1)*100/60)<>24

Thanks but as you can see all values are formatted according to the rule now, while row 9 should be not formatted since the value is 24. By the way, what is the reason that a column D is added to the data here with exactly the same values as column A?

The value in B2 and B3 should have actually been 15.75 and 8.25 respectively, so that the values for 1 November add up to 24 (and would not be formatted as a result of the CF rule). This is exactly the kind of input error that I would like to highlight with this rule, funnily enough I made it myself in the sample data.

 

[pecoflyer]

I added col D for testing reasons, nothing else. You can adapt as required in the CF rules.
The formula I suggested provides for your error and transforms 15,45 to 15.75, no worries
As for row 9 as it's date is 30/10 as is row 6, their total are higher then 24, and therefore highlighted.
So, your requirements are fulfilled, or did I miss something

To check, enter the CF formula in, say E2 and drag down

 

[Luwak]

I'm sorry but I cannot make it work. I have done what you suggested and copied the formula to E2, but whatever I do with the values, the result of the formula remains TRUE. It seems that the sumproduct itself is 0. Sorry for the sloppy data btw, I deleted line 6 now so there is only one value for 31/10, which is exactly 24, so that one should return FALSE (yet it doesn't).

 

[bosco_yip]

Or..........

Just changed Post #.4 pecoflyer's CF formula from "," into "*" (revision as per highlighted red color) :

=SUMPRODUCT(($A$2:$A$8=A2)*(INT($B$2:$B$8)+MOD($B$2:$B$8,1)*100/60))<>24

p.s. as per post #.7 description, in delete 1 line, so the range changed from A2:A9 into A2:A8

Regards
Bosco

 

[Luwak]

Thanks both, it's working now. My first insight in the power of SUMPRODUCT.

 

[pecoflyer]

Thanks Bosco. My regional settings played tricks on me again...