Create an Employee Schedule That Totals Weekly Hours in WPS Excel
Question details
The user needs to create an employee schedule in WPS Office Excel that automatically calculates each employee's total hours for the workweek.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Planning a weekly employee schedule and calculating total hours per employee.
- Observed behavior
- The schedule should show a weekly total for each employee, with shift durations summed and totals over 24 hours displayed correctly.
Before you start, decide whether each daily cell will store hours as plain numbers, decimals, or clock-in/clock-out times. If you use start and end times, add a daily duration column or helper cells so the weekly SUM formula returns accurate totals.
Add a Weekly Total Column with the SUM Formula
Use this when daily hours are already entered as numbers or decimal durations and you need each employee's weekly total.
This layout keeps employees in rows and days in columns, making it easy to sum the week with one formula per employee.
Open your WPS Excel worksheet and place employee names in column A. Enter the days of the week in cells B1:H1, then add a Weekly Total header in I1.
Click cell I2, where the first employee's weekly total will appear.
Type =SUM(B2:H2) and press Enter. WPS Excel adds all daily hour cells from Monday to Sunday.
Select I2 and drag the fill handle down through the last employee row to apply the formula to all employees.
Calculate Shift Durations When Using Start and End Times
Use this when the schedule records clock-in/clock-out times and you need the daily duration before applying the weekly total.
Create Your Weekly Employee Schedule in WPS Office Excel
Build an employee schedule in WPS Office Excel and use the SUM formula with [h]:mm formatting to calculate weekly hours accurately, including overnight shifts.
- 1. Open WPS Office Excel: Launch WPS Office, click New, and open a blank workbook.
- 2. Set up the schedule layout: Type employee names in column A and the days of the week in columns B through H, then add a Weekly Total column.
- 3. Enter daily hours or shift times: Enter each employee's daily hours, or enter shift start and end times and calculate duration per day.
- 4. Apply the weekly total formula: In the Weekly Total cell, enter =SUM(B2:H2) and fill the formula down for all employees.
- 5. Format time totals correctly: Select the weekly total cells, press Ctrl+1, choose Custom, and enter [h]:mm so totals over 24 hours display correctly.
Frequently Asked Questions
How do I total weekly hours in WPS Excel if my daily hours are just numbers?
Put employee names in column A and days in B1:H1. In the weekly total cell, enter =SUM(B2:H2), press Enter, and drag the fill handle down through all employee rows.
Why does my weekly total show 4:00 instead of 100:00?
Time values that exceed 24 hours need the custom format [h]:mm. Select the total cells, right-click and choose Format Cells, then go to Custom and enter [h]:mm.
How do I calculate shift hours when the end time is the next day?
Use =C2-B2+(C2<B2). The (C2<B2) part returns 1 when the end time is earlier than the start time, adding one day for an overnight shift.
Can I enter employees as rows and days as columns in WPS Excel?
Yes. This layout works well for weekly schedules, and you can add daily duration helper columns if you record shift start and end times instead of daily totals.
Will this employee schedule workbook open in Microsoft Excel?
Yes. WPS Office Excel saves files in the .xlsx format, so your schedule, formulas, and time formatting can be opened and edited in Microsoft Excel without conversion.
