5 Replies Latest reply on Sep 27, 2018 10:48 AM by Joe Oppelt

# Counting instances before a certain time of day

I want to see the average percentage of jobs completed before 2pm.

So for example if 200 of 300 jobs were completed before 2pm in august and 100 of 250 jobs were completed in septebmer.  I want to plot 66.6% for august, and 40% for september.

Similar to this thread Less or more than a particular day and time but I just want to count before a certain time for all days of the month not a specific date.

My attempt: SUM(IIF([Job Completed]<00:14:00, 1, 0))

I attached the workbook to the thread.

• ###### 1. Re: Counting instances before a certain time of day

In the attached I created a [Completed on time] calc.

You will want to test out a time exactly at 2:00 (I'm assuming that 2:00 on the button still wins.  If 2:00 is considered late, then you can chop out the second half of the IF logic in that calc.)

• ###### 2. Re: Counting instances before a certain time of day

Thanks Joe, that worked!

• ###### 3. Re: Counting instances before a certain time of day

Hey Joe I have one more question.

I counted the number of jobs before 2pm.  The next thing I wanted to do was to get a percentage [Completed on time?]/[Total # of jobs]

When I tried to do that I got the cannot mix aggregate and non aggregate error.  I can't remember how to solve this issue. Could you help me out again? I attached the new workbook.

• ###### 4. Re: Counting instances before a certain time of day

SUM([Completed on time?])/[Total # of jobs] ---would this get me what I'm looking for?

• ###### 5. Re: Counting instances before a certain time of day

Scott -- That's exactly it.

Notice that on your sheet the value on the TEXT shelf is a SUM().

If you dragged [Total # of jobs] onto TEXT as well, it goes on as AGG(), which means it's already an aggregate.

Here's a secret:  When you are making a calc, and if you drag those two fields from the sheet into the calc editor, they get added into the editor with the correct format.