How to Calculate Weekly Hours for Day and Overnight Shifts in WPS Spreadsheets
Question details
The user needs to calculate total weekly hours from a rota of daily start and finish times, including overnight shifts that cross midnight, using WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building or updating a weekly rota or timesheet with multiple shift start and finish pairs and needs a reliable formula for daily and weekly totals.
- Observed behavior
- Simple subtraction returns negative durations when a shift crosses midnight, so the weekly total is incorrect unless the calculation adjusts for overnight hours.
Before writing formulas, format the Start Time and Finish Time cells as Time and format the result cells as Number or General. This ensures WPS Spreadsheets treats entries as time values and displays decimal hours correctly.
Calculate Weekly Hours with MOD for Day and Overnight Shifts
Use this compact formula method when you want one reliable calculation for every shift, including those that cross midnight.
The MOD formula returns the positive remainder after division by 1, which represents the fraction of a 24-hour day. When a finish time is earlier than a start time, MOD adds one day back automatically, so the shift length stays positive.
Click the first Start Time cell, then on the Home tab open the Number Format dropdown and choose Time. Repeat for the Finish Time cells so the formulas can use the time values.
In the first Daily Hours cell, type =MOD(C2-B2,1)*24 and press Enter. This calculates the shift length in decimal hours even when the finish time is earlier than the start time.
Select the cell with the formula and drag the fill handle down through every shift pair in the rota.
Click the Total Weekly Hours cell, type =SUM(D2:D8), and press Enter. If the total exceeds 24, keep the cell in Number or General format.
Use an IF Formula to Add One Day When Finish Is Earlier Than Start
Use this method when you prefer explicit IF logic that shows exactly when a shift crosses midnight.
Calculate Weekly Shift Hours in WPS Spreadsheets with MOD and SUM
You can complete this directly in WPS Spreadsheets by storing shift start and finish times as time values, calculating each shift with the MOD formula, and summing the results for a weekly total. The same approach works for day shifts, overnight shifts, and mixed rotas.
- 1. Open your timesheet in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select your Excel or WPS worksheet that contains the shift times.
- 2. Verify time formats: Select the Start Time and Finish Time columns, open the Number Format dropdown on the Home tab, and choose Time.
- 3. Apply the recommended formula: Click the first Daily Hours cell, enter =MOD(C2-B2,1)*24, and press Enter; drag the fill handle down through all shift rows.
- 4. Sum the weekly hours: Select the Total Weekly Hours cell, type =SUM(D2:D8), and press Enter.
- 5. Save or export: Click the Save icon or Menu > Save As and choose .xlsx format to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does =MOD(FinishTime-StartTime,1)*24 work for shifts that cross midnight?
WPS Spreadsheets stores times as fractions of a day. If the finish time is earlier than the start time, subtracting gives a negative fraction, and MOD with 1 adds one day back to return a positive duration. Multiplying by 24 converts the day fraction into decimal hours.
How do I show weekly hours when the total is more than 24?
Format the total cell as Number or General instead of Time. A time format may display totals over 24 hours incorrectly, while Number format shows the decimal total correctly, such as 42.5 hours.
Can this method handle a rota with several shift pairs per day?
Yes. Put each start and finish pair on its own row or in separate columns, apply the MOD or IF formula to each pair, and then use SUM to add all daily or shift results into one weekly total.
What time format should my start and finish cells use?
Select the cells, go to the Home tab, open the Number Format dropdown, and choose Time. This stores the entries as time values instead of text, so the formula can calculate the duration correctly.
What if my shift times already include dates on different days?
If the cells contain full date and time values, subtract the start from the finish directly and multiply by 24, for example =(C2-B2)*24. MOD is mainly needed when only times are entered and overnight shifts would otherwise produce a negative result.
