Calculate Excel Shift Pay Across Different Hourly Rates
Question details
Calculate employee pay when one shift includes multiple hourly wage rates during different time periods.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to split a shift into regular, evening, and overnight wage periods, then total the pay for each applicable period.
- Observed behavior
- The calculation must correctly apply each rate to the hours worked in its corresponding wage band, including shifts that continue past midnight.
List each wage band with its exact start time, end time, and hourly rate before building the formula. Decide whether an end time is treated as the beginning of the next period, which avoids counting a boundary minute twice.
Split the shift into dated wage-band periods and calculate overlap pay
Use this method when shifts can overlap several rates or run past midnight. It is the most reliable approach because every wage band has its own actual start and end date-time.
For example, you might define regular hours as 6:00 AM–6:00 PM at £15, evening hours as 6:00 PM–10:00 PM at £20, and overnight hours as 10:00 PM–6:00 AM at £25. Replace these example times and rates with your organization’s actual pay rules.
Excel stores time as part of a day, so multiplying an overlapping time result by 24 converts it to hours.
In a worksheet, enter the employee shift start date-time in B2 and the shift end date-time in C2. If you enter time only, add the date as well so Excel can distinguish overnight work.
In D2, enter =IF(C2<B2,C2+1,C2). This returns the adjusted shift end date-time when the entered end time is earlier than the start time.
Create columns for Band Start, Band End, Hourly Rate, Hours Worked, and Pay. Enter actual date-times for each band that may overlap the shift. For an overnight band, set its Band End to the following day, such as 6:00 AM on the next date.
If Band Start is F2, Band End is G2, and the adjusted shift end is D2, enter =MAX(0,MIN($D$2,G2)-MAX($B$2,F2))*24 in I2. Copy the formula down for every wage-band row.
In J2, enter =I2*H2, where H2 contains the hourly rate. Copy down, then use =SUM(J2:J10) to return the total pay across all applicable bands.
Use helper columns for fixed regular, evening, and overnight rates
Use separate rate formulas when every shift follows the same fixed daily schedule and you want an easy-to-audit payroll sheet.
Calculate Multi-Rate Shift Pay in WPS Spreadsheets
WPS Spreadsheets lets you create date-time overlap formulas for regular, evening, and overnight wage bands. Use the same worksheet structure and familiar Excel formulas to calculate each band’s hours and total the employee’s pay, including overnight shifts.
- 1. Open a payroll worksheet in WPS Spreadsheets: Open WPS Office, select Spreadsheets, and create a new workbook or open the existing .xlsx payroll file.
- 2. Enter complete shift date-times: Add the shift start and end date-time in separate cells, then use =IF(EndTime<StartTime,EndTime+1,EndTime) to adjust an overnight end time.
- 3. Add rows for each pay period: Create rows for regular, evening, and overnight periods with their actual start date-times, end date-times, and hourly rates.
- 4. Calculate period overlap and pay: Use =MAX(0,MIN(ShiftEnd,BandEnd)-MAX(ShiftStart,BandStart))*24 for hours, then multiply the result by the wage rate.
- 5. Total the calculated pay: Use the SUM function on the pay column to return the total amount due for the shift.
Frequently Asked Questions
How do I calculate pay for a shift from 2:00 PM to 11:00 PM with different rates?
Create a separate row for every wage period the shift overlaps. Calculate the overlapping hours for each period, multiply by that period’s rate, and add the results. For example, a 2:00 PM–11:00 PM shift may include regular, evening, and overnight-rate rows depending on your schedule.
What formula handles a shift that ends after midnight?
Use =IF(EndTime<StartTime,EndTime+1,EndTime) to create an adjusted end date-time. Use this adjusted value in your overlap formulas so Excel recognizes that the shift continues into the next day.
Why does my Excel formula return a negative number of hours?
A negative result usually means the end time is earlier than the start time and Excel is treating both times as the same date. Enter full date-times or use an adjusted end-time formula that adds one day for overnight shifts.
Should the end of one wage band be included in both rates?
No. Assign the boundary time to only one band. A practical rule is that each band includes its start time and excludes its end time, preventing duplicate pay at boundaries such as 6:00 PM.
Can I use the same formulas in WPS Spreadsheets?
Yes. WPS Spreadsheets supports common Excel formulas such as IF, MAX, MIN, and SUM, and it can open and save .xlsx files used for payroll calculations.
