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.