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.
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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?
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
Last edited by amitmodi_mrt; 03-13-2008 at 05:58 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
K2 should contain the weekday start time, i.e. 08:00 and K3 should contain the end time, i.e. 17:00Code:="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")
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
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....?
Last edited by amitmodi_mrt; 03-13-2008 at 07:12 PM.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks