asked on

ms excel count days in range

I've got data thus:

call_number	vet_datetime	vet_date	vetter	time_spent
165600	01-Dec-11	01-Dec-11	Mohammed Akbar	10
168688	01-Dec-11	01-Dec-11	Chris Brown	20
170463	01-Dec-11	01-Dec-11	Hitesh Patel	17
169104	01-Dec-11	01-Dec-11	Bob Smith	14
168215	01-Dec-11	01-Dec-11	Bob Smith	15
169237	01-Dec-11	01-Dec-11	Hitesh Patel	20
169104	01-Dec-11	01-Dec-11	Duncan Halfyard	14
169105	01-Dec-11	01-Dec-11	Hitesh Patel	15

I'm looking to figure out the utilization of the vetter but can't get my head around it.

What I want to do is take the number of vets performed that day, and knowing that I have 450 minutes of working time in a day, calculate their utilization - IE, 22 vets at 10 minutes each would be 220 minutes or approx 50% utilization.

I can do this now quite easily. However, over a period of a months data some staff are absent, so I need to calculate this whilst also considering dates where the vetter did NO vets.

IE, there a 450 minutes x 20 working days for everyone. Some staff might have only worked 10 days that month, so we cannot simply look at 9000 minutes available for everyone to calculate utilization. There must be a way to "count" the number of days where a vet took place, and from that point, we know the staff member was in, therefore "add" 450 minutes to their allowance, for want of a better expression.

Can anyone help??

Thanks in advance!

Ross

barry houdini

Hello Ross,

I'm having some difficulty visualising what you want to do, exactly. You say you can do this quite easily now. Can you indicate how? How do you want to change that?

regards, barry

Rob Henson

Have you tried summarising the data with a Pivot Table?

You might then be able to extract the necessary statistics that you want.

Thanks
Rob H

Ross

ASKER

Ok let me try and explain in more detail

I have a list like above, every call a person (vetter) has done on a day. if they've done several calls on one particular day, they have a new entry one per line as above.

I know there are 450 minutes in a working day. I can calculate utilization by adding up the time spent in a day where time has been allocated to a call - so someone might have logged 225 minutes in a day, and I can say they are 50% utilized.

What i need to figure out is a way to look at that over a month. I can't compare one person with another when they might have done unequal amounts of days - we can presume they were at work if there is any entries for them that day, but if there isn't then their utilization calculation needs to be adjusted thus:

utilization = (("450" x "number of days worked that period") / "total logged time spent")

Does that make more sense?

I'm struggling with the "number of days worked in period" bit!

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ross

ASKER

Barry, that is simply priceless. I didn't notice they were array formulas to start with so that threw me but it's perfect and does exactly what I want. I can see how you've done it thanks to your example spreadsheet. Thanks very much - a great lesson for me :)