How to Distribute Work Hours Across Shifts in Excel & WPS
Question details
Calculate how many hours of a work period fall into specific shift categories (day, evening, overnight), especially when the shift crosses midnight.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking employee work hours and allocating them to different pay rates based on time of day (day, evening, or overnight).
- Observed behavior
- Needs formulas to automatically split total hours worked into respective shift buckets based on clock-in and clock-out times, correctly handling midnight transitions.
Ensure your clock-in and clock-out data is properly formatted as Time in your spreadsheet. If your shifts cross midnight, verify that your formulas account for the next-day transition.
Use the LET and TIME Functions to Allocate Shift Hours
A robust method using the LET function combined with MAX/MIN to calculate hours for day, evening, and overnight shifts, accurately handling midnight crossings.
This method defines variables within the formula to avoid repetitive calculations. We assume the Day shift is 6:00 to 18:00, Evening is 18:00 to midnight, and Overnight is midnight to 6:00. Clock-in time is in column B and Clock-out time is in column C.
In the Day shift column, enter `=LET(time_in,$B2,time_out,$C2+IF($C2<time_in,1,0),shift_start,TIME(6,0,0),shift_end,TIME(18,0,0),MAX(0,MIN(time_out,shift_end)-MAX(time_in,shift_start))+IF($C2<time_in,MAX(0,$C2-shift_start),0))`.
In the Evening shift column, enter `=LET(time_in,$B2,time_out,$C2+IF($C2<time_in,1,0),shift_start,TIME(18,0,0),shift_end,1,MAX(0,MIN(time_out,shift_end)-MAX(time_in,shift_start)))`.
In the Overnight shift column, enter `=LET(time_in,$B2,time_out,$C2+IF($C2<time_in,1,0),shift_start,TIME(0,0,0),shift_end,TIME(6,0,0),MAX(0,MIN(time_out,shift_end)-MAX(time_in,shift_start))+IF($C2<time_in,MIN($C2,shift_end),0))`.
Select the result cells, right-click, choose 'Format Cells', and set them to 'Time' format. Alternatively, multiply the entire formula by 24 and format as 'Number' to display decimal hours.
Calculate Total Hours with Standard IF Functions
An alternative approach to find the total shift duration and handle overnight logic for older spreadsheet versions.
Effortlessly Calculate Shift Hours in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and time functions like LET, MAX, MIN, and TIME, making it incredibly easy to build complex shift-tracking templates. Handle midnight crossovers seamlessly while enjoying high compatibility with Microsoft Excel.
- 1. Open your Timesheet: Launch WPS Spreadsheet and open your employee time-tracking file.
- 2. Apply Shift Formulas: Paste the provided LET formulas into your designated Day, Evening, and Overnight tracking columns.
- 3. Format and Drag: Format the calculation cells as Time or Decimal, and drag the fill handle down to apply the calculations to all rows.
Frequently Asked Questions
Why do I get a negative number or #NUM error when calculating shifts that cross midnight?
When an employee clocks in at 22:00 and out at 06:00, simply subtracting the times results in a negative value. You must add 1 (representing a full 24-hour day) to the clock-out time if it is smaller than the clock-in time.
How do I convert the calculated time format (e.g., 08:30) into decimal hours (8.5)?
Spreadsheets store time as a fraction of a 24-hour day. To convert the time value into a decimal number of hours, enclose your entire time formula in parentheses, multiply the result by 24, and change the cell format to 'Number' or 'General'.
Can I customize the start and end times for my company's specific shifts?
Yes. In the LET formulas provided, simply change the values inside the TIME() function. For example, if your day shift starts at 8 AM, change TIME(6,0,0) to TIME(8,0,0).
