Calculating Net Working Hours.

**amitmodi_mrt** · 03-13-2008, 03:27 PM

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

**daddylonglegs** · 03-13-2008, 04:05 PM

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

**amitmodi_mrt** · 03-13-2008, 04:06 PM

No, ticket can be cteated at any time.

**NBVC** · 03-13-2008, 04:07 PM

They are not pretty formulas, but you can find the one that I think will work for you at the top of this page:

http://www.cpearson.com/excel/DateTimeWS.htm

**daddylonglegs** · 03-13-2008, 04:19 PM

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?

**amitmodi_mrt** · 03-13-2008, 04:40 PM

i want to calculate how many working hours are passed and how many working hours are left to complete the ticket.
Suppose a ticket was created on 3/12/2008 10:00 AM and current time is 3/13/2008 10:00 AM so it should show "Age of Ticket = 8 hours" and "Time Remaining = 32 hours"

Lunch time is 14:00PM to 15:00PM

**daddylonglegs** · 03-13-2008, 05:49 PM

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

="Age of ticket = "&TEXT((NETWORKDAYS(A2,C2)-1)/3+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1)-MEDIAN(0,1/24,MOD(C2,1)-7/12),K$3-1/24,K$2),K$3-1/24)-MEDIAN(NETWORKDAYS(A2,A2)*(MOD(A2,1)-MEDIAN(0,1/24,MOD(A2,1)-7/12)),K$3-1/24,K$2),"[h]:mm")

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

**amitmodi_mrt** · 03-13-2008, 06:07 PM

What do you have in K$1 and K$2..?
The formulla is working but not giving exact result.. i put the start time as 3/13/2008 10:00 in A2 and =now() in C2 (My current time is 3/14/2008 3:33 AM, I am in India) it is showing "Age of ticket = 4:46"
however it should show "Age of ticket =7:00" as working hours ends at 17:00 PM.
=============================================================

I AM SORRY... IT WAS MY MISTAKE...
IT WORKED.. I DIDNT NOTICED WHAT YOU WRITTEN FOR K2 AND K3...
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU VERY MUCH......

Can i have your email id so that i can contact you if i need any help in future....?

**allen1968** · 04-24-2013, 02:58 AM

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

**Pepe Le Mokko** · 04-24-2013, 03:04 AM

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**allen1968** · 04-24-2013, 03:16 AM

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

**klohman** · 07-02-2014, 05:46 PM

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.

**amitmodi_mrt** · 02-13-2015, 03:49 AM

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.