Here is a sample data. I have multiple people who worked at different hours of the day.
The numbers 26, 56, 15 are by the formula count(distinct CASE#).
The header for date time (7/17 8PM, 7/17 9PM...) is DATE_TIME_CST.
I am trying to color code the cells based on a target of 29. I know the logic for it, but I am unable to figure out how to get the count of hours they worked in a day.
The logic I am using is
if(Count(distinct CASE#)>=(6.5/ Count(distinct DATE_TIME_CST))*29,lightGreen(),lightred())
In the above logic, Count(distinct DATE_TIME_CST) is definitely wrong as for each column, the distinct count would be 1. But I am looking for the total count of hours worked by the person in terms of DATE_TIME_CST i.e 3 in this case.
What would be the correct formula?