Calculate Machine Downtime During Business Hours in WPS Office Excel
Question details
The user needs to calculate total machine downtime in minutes, counting only Monday through Friday from 6:00 AM to 11:00 PM and excluding weekend and after-hours time.
- Product
- WPS Office Spreadsheets / Excel
- Device & OS
- not provided
- Scenario
- The user has failure and repair timestamps and needs accurate downtime minutes for operational reporting.
- Observed behavior
- Simple timestamp subtraction includes nights, weekends, and off-schedule minutes, so it overstates downtime against the 6:00 AM to 11:00 PM working window.
Confirm that failure and repair timestamps are real date/time values rather than text, and decide which columns contain the start and stop times before entering the formula.
Calculate business-hours downtime with the NETWORKDAYS formula
Use this solution when you want a standard Excel-compatible formula that works in WPS Office Spreadsheets without requiring LAMBDA or dynamic array functions.
This formula uses NETWORKDAYS to count only Monday-through-Friday full workdays and MOD to apply the 06:00-23:00 window on the start and end days. It is suitable for most WPS Office versions.
Launch WPS Office, click Sheets, open the workbook that contains the failure and repair timestamps, and confirm the start timestamps are in one column and the stop timestamps are in the next column.
Select the first result cell in a blank column, paste the following formula, and press Enter: =IF(INT(A2)=INT(B2),IF(WEEKDAY(A2,2)<=5,MAX(0,MIN(23/24,MOD(B2,1))-MAX(6/24,MOD(A2,1)))*1440,0),MAX(0,NETWORKDAYS(A2,B2)-IF(WEEKDAY(A2,2)<=5,1,0)-IF(WEEKDAY(B2,2)<=5,1,0))*1020+IF(WEEKDAY(A2,2)<=5,MAX(0,23/24-MAX(6/24,MOD(A2,1)))*1440,0)+IF(WEEKDAY(B2,2)<=5,MAX(0,MIN(23/24,MOD(B2,1))-6/24)*1440,0))
Drag the fill handle from the first result cell down through the remaining timestamp rows so each downtime record is calculated separately.
Test the result with a known case, such as Monday 07:00 to Monday 09:00, which should return 120 minutes.
Use the LAMBDA minute-sequence formula
Use this solution when your WPS Office version supports LAMBDA, LET, SEQUENCE, FILTER, and MAP, and you prefer a single dynamic formula.
Calculate Business-Hours Downtime in WPS Office Spreadsheets
WPS Office Spreadsheets can perform this downtime calculation with built-in Excel-compatible functions. Set up the failure and repair timestamp columns, apply the business-hours formula, and get downtime minutes that exclude weekends and after-hours time.
- 1. Download WPS Office: Go to wps.com, click Download, install WPS Office, and open WPS Spreadsheets.
- 2. Open the downtime workbook: Click Menu in the top-left corner, choose Open, and select the Excel workbook that contains failure and repair timestamps.
- 3. Enter the business-hours formula: Select the result cell, paste the recommended NETWORKDAYS formula, and replace A2 and B2 references with your timestamp columns.
- 4. Validate and save the report: Press Enter, fill the formula down for all rows, compare a known value, and save the file as .xlsx for Excel compatibility.
Frequently Asked Questions
Does the formula exclude public holidays?
No. The basic WEEKDAY and NETWORKDAYS formulas only exclude Saturdays and Sundays. To exclude holidays, add a holiday range to NETWORKDAYS or extend the LAMBDA filter to reference a holiday list.
What happens if downtime starts or ends outside 6:00 AM to 11:00 PM?
The formulas cap the counted time. Minutes before 6:00 AM or after 11:00 PM on weekdays are ignored, and all weekend time is excluded.
Why does my formula return 0 or an incorrect value?
Select the timestamp cells and check that they are real date/time values, not text. Use Format Cells to apply a Date/Time format, and confirm that the end time is later than the start time.
Can I change the working window to 9:00 AM to 5:00 PM?
Yes. In the NETWORKDAYS formula, replace 6/24 with 9/24 and 23/24 with 17/24, then change the full-day minute multiplier from 1020 to 480.
Does this calculation work in Microsoft Excel as well as WPS Office?
Yes. The NETWORKDAYS formula is Excel-compatible, and the LAMBDA formula works in WPS versions that support Microsoft 365-style dynamic array functions.
