WPS Spreadsheet: Calculate Employee Work Hours by Weekday and Schedule
Question details
The user needs to calculate actual employee work hours between start and finish times while applying different permitted working periods for weekdays and Saturday.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An employee's start and finish date-times must be compared with company working schedules, including weekday hours, Saturday hours, breaks, and possible holidays.
- Observed behavior
- The desired result is the number of scheduled working hours that fall between the entered start and finish times, excluding time outside the permitted schedule.
Confirm the exact weekday and Saturday working periods, break times, holiday rules, and whether the finish time is treated as inclusive or exclusive. The formula below assumes one-minute intervals, an exclusive finish time, Monday-Friday hours of 9:00-12:00 and 13:00-17:00, and Saturday hours of 9:00-13:00.
Create a custom LAMBDA for weekday and Saturday work hours
Use this solution when you want one reusable formula that counts only scheduled minutes between two date-time values.
The formula creates one-minute intervals between the start and finish values, checks each interval's weekday and time, and counts only intervals inside the permitted schedule. Replace the example times with your company's actual schedule before using it for payroll or attendance calculations.
This version does not automatically exclude holidays. Holiday dates should be added as an additional condition if your schedule contains non-working holidays.
In WPS Spreadsheet, enter the employee's starting date and time in A2 and the finishing date and time in B2. Format both cells as Date and Time so the date-time values are easy to verify.
Go to the Formulas tab, select Name Manager, choose New, and create a named formula called WorkHours.
In the Refers to box, enter: =LAMBDA(startTime,finishTime,LET(minutes,ROUND((finishTime-startTime)*1440,0),IF(minutes<=0,0,LET(times,SEQUENCE(minutes,,startTime,1/1440),weekdayNumber,WEEKDAY(times,2),clockTime,MOD(times,1),weekdayHours,(weekdayNumber<=5)*(((clockTime>=TIME(9,0,0))*(clockTime<TIME(12,0,0)))+((clockTime>=TIME(13,0,0))*(clockTime<TIME(17,0,0))))),saturdayHours,(weekdayNumber=6)*(clockTime>=TIME(9,0,0))*(clockTime<TIME(13,0,0)),SUM(--((weekdayHours+saturdayHours)>0))/60))))). Click OK to save the named formula.
Select a result cell, such as C2, and enter =WorkHours(A2,B2). Press Enter to return the total permitted hours as a decimal number, such as 6.5 for six hours and thirty minutes.
Calculate scheduled employee hours in WPS Spreadsheet
WPS Spreadsheet can apply a reusable LAMBDA formula to employee start and finish date-times, count only minutes within weekday and Saturday schedules, and return the result in hours.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an existing workbook or select New Spreadsheet.
- 2. Enter the employee times: Place the start date-time in one cell and the finish date-time in another, then use the Formulas tab to create the WorkHours named LAMBDA.
- 3. Apply the reusable formula: Enter =WorkHours(start_cell,finish_cell) in the result cell and replace the cell references with the actual start and finish cells.
- 4. Review the schedule result: Compare the returned decimal hours with the employee's schedule and update the TIME conditions for breaks, holidays, or different Saturday rules.
Frequently Asked Questions
How does the WPS Spreadsheet formula handle lunch breaks?
The formula counts separate permitted periods, such as 9:00-12:00 and 13:00-17:00, so the interval from 12:00 to 13:00 is excluded automatically.
Can I use a different schedule for Saturday?
Yes. Edit the saturdayHours condition in the WorkHours LAMBDA. For example, change TIME(9,0,0) and TIME(13,0,0) to the actual Saturday start and finish times.
Does the formula include the finish minute?
No. The formula treats the finish time as exclusive. A shift ending at 17:00 counts scheduled minutes up to, but not including, 17:00. Adjust the interval logic if your company uses an inclusive finish rule.
How can I exclude company holidays?
Create a holiday-date range, then add a condition such as ISNA(XMATCH(INT(times),HolidayDates)) to the allowed-time test. This prevents intervals whose date appears in the holiday list from being counted.
