How do I get count of the hours worked by a person to color code it?

[haneeshmarella]

Hi guys,

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?

[sunny_talwar · Accepted solution]

Try this

if(Count(distinct CASE#)>=(6.5/ Count(TOTAL <Name> distinct DATE_TIME_CST))*29,lightGreen(),lightred())

[haneeshmarella]

Guess DISTINCT is giving the error, Sunny.

[sunny_talwar]

You forgot to add <> around USER_NAME

[haneeshmarella]

Oops, right! Thanks a lot as always