I have a timesheet log with columns


An employee may enter several rows for the same date to log the different amount of time with respect to the specific project.

How can I use Conditional Formatting to display red color or warning if the sum of hours for one date is less than 8 hours? For example three rows:

| 11.08.2018 |  3  |  Work descr1 |  Project 1 |

| 11.08.2018 |  1  |  Work descr2 |  Project 2 |

| 11.08.2018 |  2  |  Work descr3 |  Project 3 |

How to highlight the cells with hours since the sum of hours for one same day
is less than 8 (3 + 1 + 2)?


2 Answers 2


This can be done purely with conditional formatting:

  1. Select cell B2.

  2. Choose "Format" from the menu, then "Conditional formatting..." from the dropdown.

  3. When the conditional formatting dialog appears, change the "Apply to range" value from "B2" to "B2:B"

  4. Under "Format cells if...," open the drop-down menu, scroll to the bottom and choose "Custom formula is..."

  5. In the box below the newly chosen "Custom formula is...," enter this formula:


  1. Use the tools in the "Formatting style" area to choose your "warning" formatting (e.g., red background with bold text, or white background with bold red text, etc.).

  2. Click the blue "Done" button.

  3. Close the conditional formatting dialog.


You will need one more column with a formula:

=QUERY(QUERY(A2:B;"select A, sum(B) group by A label sum(B)''");"select Col1 where Col2<8")

and then the custom formula for conditional formatting would be:

=IF($B2<>""; MATCH($A2:$A; $F$2:$F; 0))

demo spreadsheet: https://docs.google.com/spreadsheets/d/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.