How do I get SLA using Excel Raw Data
Sorry all I was not able to upload the file but I did take a screen shot of the layout. I need to get the SLA using the current data, exclude weekends, Holidays and anything after 7pm - 7am. You will notice the ticket number listed many times on the left hand side so I would need to count only the times for that ticket number and so on. We have to close tickets in an 8 hour time frame. So I would like it to list pass for under 8 hours or and fail for over 8 hours.
<<byundt deleted VBScript as a Topic and added VBA>>
sample-file.jpg
<<byundt deleted VBScript as a Topic and added VBA>>
sample-file.jpg
Brian B
Does it have to be VBA? You can use a regular Excel formula with the NETWORKDAYS() function to allow for weekends and holidays.
ASKER
No it does not have to be VBA, actually I would prefer not using a macro.
How are you currently calculating the duration column?
There would then have to be a comparison between total duration for Ticket Number. The total duration could be from a SUMIF function or by using a Pivot Table to summarise by ticket number.
A basic sample file would be appreciated.
There would then have to be a comparison between total duration for Ticket Number. The total duration could be from a SUMIF function or by using a Pivot Table to summarise by ticket number.
A basic sample file would be appreciated.
ASKER
It looks like they are using a pivot table to calculate the duration.
I was referring to the duration column shown in the screen shot, that doesn't look a pivot.
ASKER
Sorry, that is system generated. I assume it is calculated every time a ticket is actively worked on or the time it spends with a particular group. If the tech pends the ticket, then the clock stops and the time is calculated. Once he puts it into open-work in progress the clock starts again.
Just worked out, that is number of seconds actually elapsed between start and finish, including weekend and non-work hours.
ASKER
Here are the holidays I need to exclude.... I am not sure if the system excludes those.
Holiday
Day of Week
Date
New Year's Day Monday
2-Jan-17
Martin Luther King Day Monday
16-Jan-17
Washington's Birthday Monday
20-Feb-17
Good Friday Friday
14-Apr-17
Memorial Day Monday
29-May-17
Independence Day Tuesday
4-Jul-17
Labor Day Monday
4-Sep-17
Thanksgiving Thursday
23-Nov-17
Christmas Monday
25-Dec-17
Holiday
Day of Week
Date
New Year's Day Monday
2-Jan-17
Martin Luther King Day Monday
16-Jan-17
Washington's Birthday Monday
20-Feb-17
Good Friday Friday
14-Apr-17
Memorial Day Monday
29-May-17
Independence Day Tuesday
4-Jul-17
Labor Day Monday
4-Sep-17
Thanksgiving Thursday
23-Nov-17
Christmas Monday
25-Dec-17
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rob, this looks good, quick question, the number of cells the ticket is in on the left may change, could be more could be less, would the formula adjust to that?
The only formula that uses the ticket number is the SUMIF and that looks at the whole column for ticket number and duration.
The Pivot on the second sheet will need adjusting to cover more rows but will automatically include new ticket numbers as they occur.
The Pivot on the second sheet will need adjusting to cover more rows but will automatically include new ticket numbers as they occur.
ASKER
Thanks Rob. I am going to post another question on percentages for pass or fail. )