Calculate SLA Due Dates and Times During Business Hours in WPS Office Spreadsheets
Question details
The user needs a reliable way to calculate ticket resolution deadlines based on priority, business hours, weekdays, holidays, and time received so unused hours carry to the next business day.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Service desk or ticket management where each request has a priority, SLA hours, and received date/time, and the due date must respect working hours and non-working days.
- Observed behavior
- Simple date addition returns due dates outside business hours or on weekends and holidays; the goal is a deadline that falls within the configured business schedule.
Before you start, list your standard business hours, lunch or break periods, holiday dates, and the SLA hours assigned to each priority. This schedule information is required for the formula or workbook to return the correct next-working-day due time.
Calculate SLA due dates and times with WORKDAY.INTL and time normalization formulas
Use this formula-based method when you want a lightweight calculator that skips weekends and holidays and keeps the result within business hours.
The WORKDAY.INTL function returns the next working date after a given number of business days, and it can skip a custom weekend pattern and a holiday list. Time formulas then handle the hours carried over when a request arrives after the close time or when SLA hours exceed the remaining time in a day.
In WPS Spreadsheets, set up columns for Received Date/Time, Priority, SLA Hours, and Due Date/Time, then enter a sample ticket in row 2.
On a separate sheet, enter holiday dates in one column and the SLA hours for each priority in another table. Select each range, click Formulas > Name Manager, and define names such as HolidayList and PriorityTable.
Click the Due Date cell and type =WORKDAY.INTL(start_date, business_days, 1, HolidayList) to calculate the due date while skipping weekends and holidays.
Add time logic using TIME, MOD, and IF so the returned date/time does not fall before 8:00 AM or after 5:00 PM. If the calculated time exceeds the close time, move the excess hours to the next WORKDAY.INTL result.
Build a reusable SLA due-date workbook with priority, holiday, and working-hour tables
Use this approach when you manage ongoing SLA calculations and want a configurable workbook instead of editing complex formulas for every ticket.
Calculate SLA Due Dates and Times in WPS Office Spreadsheets
WPS Office Spreadsheets can directly calculate SLA due dates and times using WORKDAY.INTL, WORKDAY, VLOOKUP, and time normalization formulas. Set up business hours, holidays, and priority mappings, then let the workbook return the correct next-working-day deadline.
- 1. Install WPS Office: Download WPS Office from the official WPS website and install WPS Spreadsheets on your computer.
- 2. Open the SLA workbook: Launch WPS Spreadsheets, click Menu > Open, and browse to your existing Excel .xlsx SLA file.
- 3. Enter or adjust the SLA formulas: Select the due date cell and use the formula bar to enter WORKDAY.INTL, VLOOKUP, and time normalization formulas based on your business schedule.
- 4. Save as an Excel workbook: Click Menu > Save As and choose Excel Workbook (*.xlsx) to keep full Microsoft Excel compatibility.
Frequently Asked Questions
How do I make SLA due dates skip weekends and holidays in WPS Spreadsheets?
Use the WORKDAY.INTL function with a weekend code and a holiday range. For example, =WORKDAY.INTL(C2, D2, 1, HolidayList) adds the required business days while skipping Saturdays, Sundays, and the listed holidays.
Why does my calculated SLA time continue past 5:00 PM?
A plain addition formula adds clock hours without checking the working schedule. Add time normalization logic so that when the calculated time exceeds the close time, the excess hours are carried to the next WORKDAY.INTL result.
Can WPS Spreadsheets handle multiple SLA priorities such as Low, Medium, and High?
Yes. Store the priority-to-hours mapping in a table and use VLOOKUP or IF to return the correct SLA hours for each priority, then feed that value into the WORKDAY.INTL and time formulas.
Can I open an Excel SLA template that uses macros in WPS Office?
WPS Office supports Excel workbooks and many formulas, but macro support can vary by version and file type. For predictable SLA calculations, a formula-based workbook is easier to maintain across WPS Spreadsheets and Microsoft Excel.
