'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate SLA Working Hours with Formula in Excel
Question details
The user needs to calculate the exact business minutes between a support call and a technician's arrival, excluding non-working hours, weekends, and public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking Service Level Agreement (SLA) compliance by calculating exact business minutes for issue resolution.
- Observed behavior
- Calculating raw time difference yields incorrect SLA data because it includes nights, weekends, and holidays.
Ensure your start and end dates contain both date and time values, and set up a dedicated range of cells listing your specific public holiday dates.
Use Nested IF and WEEKDAY Formula for SLA Calculation
Calculate total business minutes by evaluating start and end dates against a defined schedule and holiday list.
This formula breaks down the calculation into same-day responses versus multi-day responses. It uses the WEEKDAY function to determine different daily working schedules (e.g., shorter hours on weekends) and checks a predefined range for public holidays.
Ensure cell A2 contains the start date/time, cell B2 contains the end date/time, and cells E2:E27 list your company's holiday dates.
Click on the cell where you want the SLA minutes displayed (e.g., C2).
Type the formula exactly as follows: =IF(INT(A2)=INT(B2),(B2-A2),IF(OR(WEEKDAY(A2,2)=7,INT(A2)=$E$2:$E$27),16/24-MOD(A2,1),IF(WEEKDAY(A2,2)=6,17/24-MOD(A2,1),18/24-MOD(A2,1)))+(MOD(B2,1)-8/24))*24*60
Press Enter to execute the formula. Format the result cell as a 'Number' rather than a date to view the total elapsed business minutes.
Calculate Complex SLAs Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, allowing you to track SLA compliance accurately. Easily set up custom working hours and holiday lists with a familiar, user-friendly interface.
- 1. Open your SLA workbook: Launch WPS Spreadsheet and open the document containing your SLA logs.
- 2. Format date and time cells: Right-click your date columns, choose 'Format Cells', and ensure they are set to a custom format that displays both date and time (e.g., yyyy/mm/dd hh:mm).
- 3. Apply the calculation formula: Paste the SLA nested IF formula into your target cell and press Enter to instantly calculate the business minutes.
- 4. Drag to fill: Click and hold the fill handle at the bottom right corner of the cell, then drag it down to automatically calculate SLA times for all recorded support calls.
Frequently Asked Questions
Why does my SLA calculation return a negative number?
This usually happens if the end date is earlier than the start date, or if the start time is outside the standard working hours specified in the formula. Verify your data entries to ensure they fall within business hours.
Can I use the NETWORKDAYS function for SLA calculation instead?
Yes, if you only need to calculate full business days. However, NETWORKDAYS cannot calculate exact hours and minutes natively without combining it with complex fractional arithmetic.
How do I adjust the formula if my business operates 24/7?
For a 24/7 schedule, you do not need to subtract non-working hours or weekends. You can simply use the formula =(B2-A2)*24*60 to get the total elapsed minutes.
Why does the formula multiply the result by 24 and 60?
Spreadsheet software stores time as a fraction of a day. Multiplying by 24 converts the fraction into total hours, and multiplying by 60 converts those hours into minutes.
