Excel and Time

[quig699]

I have an assignment to find a way to take an employees schedule and see how much time they are logged into our phone system taking calls for every two hour period. I know it sounds simple but our schedules change daily so it is never a constant. I have the representatives start and end time, as well as their breaks and lunches. An example of what I am looking for is say you are looking at the 9am to 11am time peroid. If the rep is on a break from 9:30 to 9:45 they would only be on the phone for 1:45. Is there a way in excel to calculate this? I have racked my brains over this all weekend but have not had any luck.

Thank You

Amy =)

Thanks,

Amy

 

[xlhelp]

Amy.

What you are asking is fairly complicated. I devised a spreadsheet for our call center a few years back. (I got paid a lot for it and as such, it is now proprietary and I can't share). What you need to do is match your schedule (hopefully a spreadhseet) against phone logins. Depending on the phone system your company uses there are probably an obscene amount of reports it generates. You would need to take into account not only breaks, but MST (meetings, sickness & training). I didn't mention vacation since that would be accounted for in the schedule.

No idea what you are trying to accomplish. But industry standards say that over a span of of 1 year you cannot get any more than an average of 70% (approximate) efficiency from your call center employees. (Our comapany does close to 80%, but there are a lot of incentives for these employees to perform. Eventually they burn out and the result is low retention). When you start expecting more, one of VMST comes into play; most likely sickness.

BTW, you can look up industry stats on the internet. Probably the HelpDesk Institute.

A man has only two choices: He can be right or he can be happy.