Reply With QuoteI dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?
SLA
Critical = 4 hours
High=8 hours
Medium= 72 hours
Low=120 hours
If the ticket is closed within the SLA then the result should be “Within SLA” else “Exceeding SLA”
Attached is the only data available through the system. I tried to use the status, priority, created date and last modified date to achieve this but no luck.
Yellow cells indicate data extract from the system.
Appreciate if someone could help me with the available data.
I dont' follow what you're asking. You already seem to have formulas to get that in the Violation column?
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Do you have a formula to exclude business hours. I want to exclude the hours from 5 PM - 8:00 AM.
What about weekends and public holidays ? Shouldn't you exclude these as well?
Hi ethelk2044
Do you mean the total hours between 2 of your dates. Perhaps the following!
=(NETWORKDAYS(StartDate/Time,EndDate/Time-1,Holidays)*("17:00"-"08:00")+(MOD(EndDate/Time,1)-MOD(StartDate/Time,1)))*24
I need to exclude the hours from 5 PM till 8 AM Monday - Thursday. I also need to exclude the hours from 5 PM on Friday till 8 AM on Monday.
Originally Posted by Kevin@Radstock
So what would be your expected result for the date and times below be!
29/03/2013 09:00:00 - 02/04/2013 14:00:00
I found a formula that works but do not understand it.I want to learn what is going on so I will be able to create other formulas. Can someone break down what the formula is doing.
B2 = Create Date
C2 = Resolved Date
5PM - 8 AM are the hours that need to be excluded because the SLA stops at 5 and starts up again at 8 AM. The business hours are 8 - 5 Monday - Friday. Therefore we would exclude the weekends as well.
NETWORKDAYS(B2,C2,Holidays)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(C2,C2,Holidays),MEDIAN(MOD(C2,1),"08:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2,Holidays)*MOD(B2,1),"08:00","17:00")
Excel 2010, Table, Defined Names
Accounts for SLA priority, working hours, lunch break, Sat/Sun and holidays.
http://58918aa17d4002474361-7ac2b7ee.../05_10_13.xlsx
If you get *.zip, don't unzip, just rename *.xlsx
In your formula what does the @prioritytxt sign mean in =SUMIFS(Table3[Priority],Table3[P_Txt],[@PriorityTxt]), is that some kind of name list reference, if so how did you create,
Posting Permissions
Bookmarks