tommythemook
New Member
- Joined
- Jul 15, 2010
- Messages
- 2
Hi
Background: I have a excel sheet in which I track work allocation. In one cell I record the date and time that an email is received from a customer. In another cell I record the date and time that the email is allocated to a staff member for a response to be issued. There is an SLA of 2 working hours for the email to be allocated to a staff member.
Problem: In order to measure performance against SLA I need a formula which returns the number of working hours in between the receipt of the email and the time of allocation to a staff member. It needs to take into account the working hours of 8am to 8pm Monday to Friday and 8am to 6pm on Saturday.
Example: Cell A1 timestamps the receipt of an email as 17/07/2010 17:00 (Saturday). Cell A2 timestamps the allocation of the email to staff member as 19/07/2010 10:00. The formula in cell A3 needs to return 3 working hours. 1 hour up to 6pm on Sat, no hours Sun, then 2 hours on Monday morning.
I cant work out how to do this and would greatly appreciate any and all suggestions.
Many thanks
Tom
Background: I have a excel sheet in which I track work allocation. In one cell I record the date and time that an email is received from a customer. In another cell I record the date and time that the email is allocated to a staff member for a response to be issued. There is an SLA of 2 working hours for the email to be allocated to a staff member.
Problem: In order to measure performance against SLA I need a formula which returns the number of working hours in between the receipt of the email and the time of allocation to a staff member. It needs to take into account the working hours of 8am to 8pm Monday to Friday and 8am to 6pm on Saturday.
Example: Cell A1 timestamps the receipt of an email as 17/07/2010 17:00 (Saturday). Cell A2 timestamps the allocation of the email to staff member as 19/07/2010 10:00. The formula in cell A3 needs to return 3 working hours. 1 hour up to 6pm on Sat, no hours Sun, then 2 hours on Monday morning.
I cant work out how to do this and would greatly appreciate any and all suggestions.
Many thanks
Tom
