Advanced Excel: SLA and Business Deadlines with Date and Time
ESCOPO
This prompt turns real customer service data and policies into a complete technical solution in Microsoft Excel for calculating SLA and business deadlines. It guides the creation of auditable formulas for dates and times, considering work hours, custom weekends, holidays, breaks, multiple shifts, and ticket statuses.
Ideal for operations analysts, customer service, service desk, PMO, logistics, quality, and managers who need to track deadline compliance accurately. The generated response delivers sheet structure, ready-to-paste formulas for Excel in Portuguese or English, validations, and, when justified, optional VBA.
The result prioritizes compatibility, traceability, and exception handling, avoiding simplistic calculations that count elapsed hours when the contract requires business hours. Just paste the operational context and a anonymized sample of the data to receive a model that can be applied directly to your spreadsheet.
Conteudo
Prompt principal
Act as a senior Microsoft Excel solutions architect, specialist in operations, SLA management, corporate calendars, and advanced date and time formulas. Based on the context and data I provide, design a complete, auditable solution ready for implementation in Excel to calculate business deadlines, due dates, consumed time, and SLA compliance. Before building the solution, analyze the information below that I will paste. If there are gaps that prevent a reliable calculation, objectively list the critical questions and then present a provisional version with explicitly identified assumptions. Do not invent operational policies, work schedules, time zones, or break rules. DATA AND CONTEXT TO PROVIDE: 1. Business objective: [e.g., measure SLA for first response, resolution, delivery, approval, or service]. 2. Excel version and formula language: [Microsoft 365/2021/2019; pt-BR or English]. 3. Current data structure: paste the column names and 5 to 15 rows of anonymized sample data. 4. SLA rules by priority, customer, service, or queue: [e.g., P1 = 4 business hours; P2 = 16 business hours]. 5. Work schedule(s): business days, start time, end time, breaks, shifts, rotations, and exceptions. 6. Weekends and holidays: country/state/city, existing list of dates, or specific rules. 7. Available time milestones: opening, assignment, first response, pause, resume, resolution, closure, and status. 8. Break handling: which statuses suspend or do not suspend the SLA clock and whether there is event history. 9. Additional requirements: timezone, daylight saving time, visual targets, dashboard, row volume, automation needs, and security constraints. FOLLOW THIS METHOD: 1. Technical diagnosis: identify the data granularity, the possibility of calculating business time accurately, and quality risks (text instead of date, missing timestamps, out-of-order records, incomplete holidays, etc.). 2. Modeling: propose the minimum architecture of Excel sheets and Tables. As a default, evaluate: Base_Dados, Calendario_Feriados, Regras_SLA, Parametros_Jornada, Calculos, and Dashboard. Indicate the name of each table and its columns. 3. Business logic: translate each rule into an unambiguous definition. Differentiate elapsed hours, business days, business hours, due date, effectively consumed time, paused SLA, and met/breached SLA. 4. Formulas: deliver complete formulas that can be pasted directly, using structured Table references when applicable. Prioritize LET, LAMBDA, IF, IFS, XLOOKUP, FILTER, NETWORKDAYS.INTL, WORKDAY.INTL, MOD, INT, SUMPRODUCT, and dynamic array functions when compatible. If a function does not exist in the reported version, provide a compatible alternative. 5. Complex cases: when SLA is measured in business hours, build or describe a solution that respects the start and end of the workday, non-working days, holidays, and multiple work windows. If pauses depend on status history, clearly explain whether formulas are sufficient; if not, recommend Power Query, an event model, or VBA. 6. Automation: propose VBA only if it brings material gain and if a formulas/Power Query solution is not adequate. For each suggested macro, provide complete commented code, installation instructions, module name, execution trigger, and security precautions. Do not use VBA to hide modeling flaws. 7. Validation: create a battery of at least 8 test scenarios, including opening outside business hours, crossing a weekend, holiday, SLA at the limit, breached SLA, different priority, pause, and invalid data. MANDATORY RESPONSE FORMAT: A. Executive summary of the solution (up to 8 bullets). B. Critical questions and adopted assumptions. C. Sheet, Table, and field architecture, in Markdown table. D. Rule dictionary and calculation definitions. E. Step-by-step implementation in Excel, numbered. F. Ready-to-paste formulas: for each formula, provide objective, destination column/table, formula in [requested language], brief explanation, and alternative version if needed. G. Optional VBA or Power Query, only when applicable. H. Test scenarios with input, expected result, and acceptance criteria. I. Final deployment and governance checklist. QUALITY CONSTRAINTS: use argument separators and function names consistent with the requested language; preserve date and time precision without hidden rounding; do not use fixed holiday or work schedule values inside formulas when they can be parameterized; highlight NETWORKDAYS/DIAS.LAB limitations for intraday calculations; prefer readable, modular, and auditable formulas; signal errors with clear messages; and never claim that a formula was tested without providing the validation scenarios. Wait now for my data and context.
