How to Calculate Work Hours in WPS Office Spreadsheets When the Employee List Changes
Question details
The user needs a maintainable spreadsheet structure in WPS Office Spreadsheets to calculate monthly work-in-progress hours when employees frequently join or leave and projects change.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user tracks work-in-progress hours for a changing team and needs reliable summaries by employee, project, month, or year-to-date without rebuilding the workbook every time.
- Observed behavior
- A reusable transaction table supports monthly, year-to-date, employee, and project hour summaries even as the employee list changes.
Before you start, decide whether the required hour totals are by employee, project, month, or a combination, and collect sample time entries with dates, employee names, project names, and hours worked. Keep the source data in separate columns so it can be converted into one normalized table.
Use a normalized transaction table with SUMIFS to calculate work hours dynamically
Best when you want a low-maintenance, formula-driven report that automatically updates when employees or projects are added.
Store source data in one table with columns for Employee, Project, Date, and Hours Worked. Add new employees as new rows rather than adding columns, so formulas and summaries do not need to be redesigned.
Open WPS Office Spreadsheets and create a new sheet named Hours. In row 1 enter the columns Employee, Project, Date, and Hours Worked, then enter one row per employee, project, date, and hours worked combination.
Select the data range, click Insert > Table, check My table has headers, and click OK. This automatically expands when new employee or project rows are added.
In the Hours table, add a Month column and enter =TEXT(C2,"YYYY-MM") if Date is in column C, so SUMIFS can group by month.
On a Summary sheet, create Employee, Project, or Month labels, then enter a formula such as =SUMIFS(Hours[Hours Worked],Hours[Employee],A2,Hours[Month],B$1) to total hours by the selected criteria.
Add new rows to the Hours table instead of adding new columns. The table and SUMIFS ranges expand automatically, so the report remains accurate.
Build a PivotTable report for flexible employee, project, and month totals
Use this when you need an interactive summary and do not want to write formulas for each combination.
Use Power Query to append and transform source data
Use this when source time entries arrive in different files or sheets, or when you want repeatable cleanup before calculating.
Track changing employee work hours in WPS Office Spreadsheets
Use WPS Office Spreadsheets to keep one normalized table and build dynamic work-hour summaries with SUMIFS, PivotTables, or Power Query. The workbook stays maintainable when employees join, leave, or switch projects.
- 1. Download and install WPS Office: Get WPS Office from the official WPS site, install it, and open WPS Office Spreadsheets.
- 2. Create the normalized hours table: Set up columns for Employee, Project, Date, and Hours Worked and enter all time entries as rows.
- 3. Add your summary formulas or PivotTable: Use SUMIFS or Insert > PivotTable to generate employee, project, and monthly totals.
- 4. Save as an Excel-compatible file: Click Menu > Save As and choose .xlsx so you can share the workbook with Microsoft Excel users.
Frequently Asked Questions
Do I need a separate sheet for each month to calculate work hours?
No. Use one transaction table with a Date column and group by month using a helper column, PivotTable date grouping, or a SUMIFS month criterion.
What formula can I use to total hours by employee and month?
If the table is named Hours and the summary has employee in A2 and month in B1, use =SUMIFS(Hours[Hours Worked],Hours[Employee],A2,Hours[Month],B$1) after adding a Month helper column.
How do I add an employee who joins without editing the formulas?
Add the employee as a new row in the source table. If the source range is formatted as a table, formulas and PivotTables that reference the table expand automatically.
Can WPS Office open and edit an Excel workbook that already has these calculations?
Yes. WPS Office Spreadsheets supports .xlsx, SUMIFS, PivotTables, and Power Query, so existing Excel workbooks can usually be opened, edited, and saved without changing the structure.
What is the best way to total hours by project instead of by employee?
Create a PivotTable from the normalized table and drag Project to Rows and Hours Worked to Values; or use SUMIFS with the Project column as the criteria range.
