Hi All,
I知 newbie to excel & have been asked by my team to calculate SLA for tickets we work on. I searched through various forums & after lot of trail & errors, I知 requesting you guys for help.
My excel sheet has following columns:
- Column B: Ticket Assignment Start Date & Time
- Column C: Ticket Assignment Stop Date & Time
- Column D: Total time spent on ticket considering working hours for each day
- Column F: Holiday List in dd-mmm-yy format
- Column G: Weekday; Column H: Start Time1; Column I: Stop Time1; Column J: Start Time2; Column K: Stop Time2
Day-Wise Business Hours (HH:MM:SS):
- Sun: 10:00:00 to 20:00:00
- Mon: 10:00:00 to 23:59:59
- Tue: 00:00:00 to 06:00:00 & 10:00:00 to 23:59:59 (No coverage b/w 6AM to 10AM)
- Wed: 00:00:00 to 06:00:00 & 10:00:00 to 23:59:59 (No coverage b/w 6AM to 10AM)
- Thu: 00:00:00 to 06:00:00 & 10:00:00 to 23:59:59 (No coverage b/w 6AM to 10AM)
- Fri: 00:00:00 to 06:00:00 & 10:00:00 to 23:59:59 (No coverage b/w 6AM to 10AM)
- Sat: 00:00:00 to 06:00:00 & 10:00:00 to 20:00:00 (No coverage b/w 6AM to 10AM & 8PM to 12AM)
As we have various set of start time & end time varying on each day of the week & also no coverage windows in the middle, I could not find anything that will be suitable soultion for my requirement.
Apologies, if I have posted the question in wrong forum.
Any help is greatly anticipated and appreciated.
Thanks in advance.
Bookmarks