Looking to create a spreadsheet based off strict SLA guidelines.
Some basic conditions applies( holidays, Sundays and time of ticket work order creation)
If ticket created is created Monday through Thursday before 9pm est., the SLA starts at the ticket create time has 24 hours to be onsite and 48 hours towards resolution
If ticket is created Monday - Thursday after 9pm est., then the SLA starts the next day at 8am est. and has 24 hours to be onsite and 48 hours towards resolution
If the ticket is created on Friday before 11pm est., the SLA starts at the ticket create time has 24 hours to be onsite and 48 hours towards resolution
If the ticket is created on Friday after 11pm but before 8am Saturday, the SLA starts at 8am and has 24 hours to be onsite and 48 hours towards resolution
If the ticket is created between 8am Saturday and 8am Monday, the SLA starts Monday at 8am has 24 hours to be onsite and 48 hours towards resolution
National recognized holidays and Sundays do not count towards SLA
Spreadsheet breakdown:
Create date and time is located in V2
Completed date and time is located in T2
AE2 - Christmas Holiday
I might not be 100% accurate on the holidays and sundays, but I have this as of right now.
=NETWORKDAYS.INTL(V2,T2,11,$AE$2)-MOD(V2,1)
End goal is to apply this to all workorders through the month to calculate SLA on a faster method than more of a current manual process.
Any help would be appreciated!
***UPDATED SPREADSHEET***
Bookmarks