Formula to analyze data?

I tried to use a pivot table. Although really neat, I do not know enough about them to get the information I need from it.

I'm trying to determine the average time it takes "EW" to build a "GWD" based on an 8 hour day of work. I'm thinking 'total number of days "EW" built one or more "GWD"' / 'total number of "GWD" "EW" built' * '8 hours in a day' = 6.667 hours per unit!

I just can't figure out how to get the first number. Obviously, this is very little data and a person could just count. This is just an example to get me started analyzing this kind of thing.

Could someone give me a clue on where to start or even what function would be helpful here. Just need a nudge to get me going.

Book1.xlsx

I can not figure it out with or without a pivot table. I don't necessarily have to use a pivot table if it can be done with just a formula.

My main problem is trying to get a formula that will count the number of different days that certain criteria is met. I can only make one to count the number of times certain criteria is met.

Thanks for helping.

Book1.xlsx

Maybe I'm trying to analyze the data in the wrong manner.

The building of a unit is to be assumed to start at the beginning of a work day I guess. A work day is 8 hours long. The information in columns D, E and F are options that would not affect build time. The average build times are known from past studies and are as follows:

8 Ton 2.5 hours
PTO 2.5 hours
NT 2.5 hours
GWD 6.5 hours

The purpose of the formula (unknown) is to see if an employee is hitting average build times. I understand that a person can't build 2 GWDs in an 8 hour day but the data is entered at the completion of a unit therefore one employee can complete 2 GWDs in one day. Or 3 PTOs in one day.

Ok... The criteria I was looking for was (B3:B57,"EW") and (C3:C57,"GWD") and only count different days. So, an answer of 10.

But, maybe I should analyze it differently by maybe applying the average build times to every unit built, sorting by employee of course, and comparing that to the number of hours worked to get a percentage of productive work time per employee. ???

I deleted a few dates in the table thinking that would make it easier, but I still can't figure it out. Another problem I'm having is that "EW" only worked 14 of the last 15 working days and I don't want to count those 8 hours as non productive because he was not on the clock for those 8 hours. I can not figure out a formula that would count the number of days that "EW" worked out of the last three weeks.

I'm in over my head.

Attachment 178221

Book1.xlsx

I put the dates back because when I filter to look at the Unit Built, there wasn't any date beside them.

I really appreciate your help.