Office 2016 Excel工单SLA统计公式需求

Alright, let's work through this Excel 2016 SLA calculation for your ticket system. I’ll break this down into manageable steps so you can follow along easily, covering all your requirements: Monday-Saturday workdays, holiday exclusion, SLA only triggers for tickets created between 08:00-20:00 on workdays, and categorization into R1/R2/R3/Out of SLA.

First, let's set up your data structure (adjust cell references to match your sheet):

• Ticket creation time: Column A (starting at A2)
• Ticket resolution time: Column B (starting at B2)
• Holiday list: A dedicated column (e.g., F2:F100) — select this range and name it Holidays (type the name in the formula bar and hit Enter to define the named range).

Step 1: Check if SLA should trigger #

In cell C2, paste this formula and drag it down to apply to all tickets. It returns 1 if the ticket meets the SLA start conditions, 0 otherwise:

=IF(AND(
    WEEKDAY(A2,2)<=6,  // 1=Monday, 6=Saturday — ensures it's a workday
    NOT(ISNUMBER(MATCH(INT(A2),Holidays,0))),  // Excludes holidays (matches the date part of creation time)
    TIME(HOUR(A2),MINUTE(A2),SECOND(A2))>=TIME(8,0,0),  // Created at or after 08:00
    TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIME(20,0,0)   // Created at or before 20:00
), 1, 0)

Step 2: Calculate effective SLA working hours #

In cell D2, use this formula to compute the total valid working hours towards SLA (only runs if SLA is triggered):

=IF(C2=0,0,
    // Remaining hours on ticket creation day (from creation time to 20:00)
    MAX(0,(TIME(20,0,0)-TIME(HOUR(A2),MINUTE(A2),SECOND(A2)))*24) +
    // Hours worked on ticket resolution day (from 08:00 to resolution time)
    MAX(0,(TIME(HOUR(B2),MINUTE(B2),SECOND(B2))-TIME(8,0,0)))*24 +
    // Total hours from full workdays between creation and resolution
    NETWORKDAYS.INTL(INT(A2)+1,INT(B2)-1,"0000001",Holidays)*12
)

Quick breakdown: #

• NETWORKDAYS.INTL uses "0000001" to set Monday-Saturday as workdays (0 = work, 1 = rest; the 7th character is Sunday)
• Each full workday contributes 12 hours (08:00-20:00)
• MAX(0,...) ensures we don't count negative time (e.g., if a ticket is resolved before 08:00 on a workday, that day contributes 0 hours)

Step 3: Categorize into SLA tiers #

In cell E2, paste this formula to assign the ticket to the correct SLA category (or 0 if SLA didn't trigger):

=IF(C2=0,0,
    IF(D2<=24,"R1",
        IF(D2<=48,"R2",
            IF(D2<=72,"R3","Out of SLA")
        )
    )
)

Step 4: Count tickets per SLA tier #

Use COUNTIF to tally how many tickets fall into each category. For example:

• R1 count (cell G2): =COUNTIF(E:E,"R1")
• R2 count (cell G3): =COUNTIF(E:E,"R2")
• R3 count (cell G4): =COUNTIF(E:E,"R3")
• Out of SLA count (cell G5): =COUNTIF(E:E,"Out of SLA")

内容的提问来源于stack exchange，提问作者Alessandro