Calculate Monthly Roster Hours in Excel From Shift Codes
Question details
Calculate employee monthly working hours automatically from roster shift codes that represent different hour values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A roster contains codes such as AM, PM, DO, and ND, and the user needs to convert them to hours and total the results by employee and month.
- Observed behavior
- Shift codes are text values, so they must be mapped to their corresponding working-hour values before they can be summed.
Create a small shift-code reference table with one column for the code and one column for its hour value. Confirm that roster entries use the same spelling and do not contain unwanted spaces.
Use XLOOKUP to convert each shift code to working hours
Use this method when each roster cell contains one shift code and you want a calculated hours value beside it.
A lookup table lets you manage shift lengths in one place. For example, set AM and PM to 8.5, DO to 0, and ND to 10, then use a formula to return the matching value for every roster code.
On the same sheet or a separate sheet, enter shift codes in H2:H5 and their hours in I2:I5. For example, enter AM, DO, PM, and ND in column H, with 8.5, 0, 8.5, and 10 in column I.
If the first roster code is in B2, select the matching hours cell and enter =XLOOKUP(B2,$H$2:$H$5,$I$2:$I$5,0). The final 0 returns zero when a code is not found.
Drag the fill handle from the formula cell across or down the hours area so every shift code returns its assigned number of hours.
Select a cell at the end of an employee’s hours row and use =SUM(C2:AG2), adjusting the range to include all calculated daily-hours cells for the month.
Summarize employee monthly hours with a PivotTable
Use a PivotTable after converting codes to hours when you need totals grouped by employee, month, department, or other roster fields.
Calculate Shift-Code Hours and Monthly Totals in WPS Spreadsheets
WPS Spreadsheets can convert roster codes into numeric shift hours with a lookup formula and calculate each employee’s monthly total. Create a code-to-hours table, apply XLOOKUP or VLOOKUP, and use SUM or a PivotTable for reporting.
- 1. Open the roster in WPS Spreadsheets: Open the employee roster workbook and identify the cells containing the daily shift codes.
- 2. Create the code and hours reference list: In an unused range or a separate worksheet, enter each shift code in one column and its hourly value in the next column.
- 3. Return hours for each shift code: In the calculated-hours column, enter =XLOOKUP(B2,$H$2:$H$5,$I$2:$I$5,0), replacing B2 and the lookup ranges with your roster locations.
- 4. Calculate each employee’s monthly total: Use the SUM function on the row or column of calculated hours, such as =SUM(C2:AG2).
- 5. Create a summary report if needed: Choose Insert > PivotTable and summarize the Calculated Hours field by employee and month.
Frequently Asked Questions
What formula converts a roster shift code into hours?
Use XLOOKUP with a code-to-hours table. For example, if B2 contains the code and H2:I5 contains the lookup table, use =XLOOKUP(B2,$H$2:$H$5,$I$2:$I$5,0).
How do I assign zero hours to a day-off code such as DO?
Add DO to the shift-code column in the lookup table and enter 0 in its corresponding hours cell. The lookup formula will then return zero for every DO entry.
Why does my XLOOKUP formula return zero for a valid shift code?
Check that the roster code exactly matches the lookup-table code. Use TRIM on imported values if needed, such as =XLOOKUP(TRIM(B2),$H$2:$H$5,$I$2:$I$5,0).
Can I calculate totals for several employees at once?
Yes. Copy the lookup formula for all roster entries, then use SUM for each employee row or create a PivotTable with Employee in Rows and Calculated Hours in Values.
Can I use VLOOKUP instead of XLOOKUP for shift hours?
Yes. If the codes are in H2:H5 and hours are in I2:I5, use =VLOOKUP(B2,$H$2:$I$5,2,FALSE). The code column must be the first column of the selected lookup range.
