We help IT Professionals succeed at work.
Get Started

ms excel count days in range

Ross asked
Last Modified: 2012-05-22
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!

Watch Question
Most Valuable Expert 2013
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE