Calculating Net Working Hours.

Hi,
I am working in call center, and i get tickets and i have to meet my Services Level Agreement (SLA) with in pre defined working hours.
My working hours are 8:00 AM to 17:00 PM, Monday to Friday. (this includes 1 hour of Lunch time which is not considered as business hour)
As per the SLA, i have to complete the ticket in 40 business hours, i.e 5 business days.
Suppose a ticket is created on 3/5/2008 10:00 AM. i want to know how much time is left for me to work on that particular ticket.

Can anyone help me in this ? PLEASE.
Please mail back to amitmodi_mrt@yahoo.com

Will the ticket always be created within the business hours? If so then with ticket start time/date in A2 try this formula to get the time that it must be completed

=WORKDAY(A2,5)+MOD(A2,1)

format to show date and time

Note: unless you're using Excel 2007 then WORKDAY is part of Analysis ToolPak add-in, you may have to enable this

No, ticket can be cteated at any time.

I don't think Chip's formula will work when the ticket can be created at any time.

Can you clarify what you actually want to calculate, do you want to calculate the time and date that you must finish the job or do you want to calculate how many working hours have passed since the ticket was created (and thereby calculate how many hours you have left)?

The latter is easier to do.

What time is lunch?

OK, this formula is a little complex....

Assuming the ticket start time/date is in A2 and in C2 you have the current time [ use =NOW()] then use this formula in D2 for time passed

Please Login or Register  to view this content.

K2 should contain the weekday start time, i.e. 08:00 and K3 should contain the end time, i.e. 17:00

Then for time remaining use this formula

=IF(MID(D2,17,5)+0>="40:00"+0,"Late","Time Remaining = "&TEXT("40:00"-MID(D2,17,5),"[h]:mm"))

Note: NETWORKDAYS is part of Analysis ToolPak add-in

I prefer to keep any communication within the forum. If you have any further queries on this subject then just post another reply to this thread....or you can PM me if I don't respond

i'm glad i found this forum as i have a similar problem..
what if working times don't end at 5pm?.. that is a ticket can still be worked on after 5pm.. how do you compute then the time passed? thanks in advance

my apologies.. will start a new one... thanks

This is the first formula I've used that's worked for my business purposes. Thank you!! I believe I've tried at least 20 others in the past week. I needed something that took weekends, holidays, and business hours into consideration but didn't "break" when an activity was started after business hours.

I made a few modifications because we have an 11 hour workday and folks covering lunches.

=IF($G2>0,TEXT((NETWORKDAYS($C2,$G2,$V$2:$V$8)-1)*11/24+IF(NETWORKDAYS($G2,$G2,$V$2:$V$8),MEDIAN(MOD($G2,1)-MEDIAN(0,1/24,MOD($G2,1)),$AA$1-1/24,$Y$1),$AA$1-1/24)-MEDIAN(NETWORKDAYS($C2,$C2,$V$2:$V$8)*(MOD($C2,1)-MEDIAN(0,1/24,MOD($C2,1))),$AA$1-1/24,$Y$1),"[h]:mm"),"N/A")

G2 is the activity end date/time.
C2 is the activity start date/time.
V2:V8 are holidays.
Y1 is the start of the business day.
AA1 is the end of the business day.

Hi Klohman,

In the above formula, how can i include the lunch hours as well. Means I just want to remove the weekends (saturday and sunday) and non business hours.

Regards,
Amit.