Function Problems

How to Categorize Shifts (Day, Night, Mid) Using Excel Formulas

Huda Qurayshi Oct 16, 2026 868 views

Question details

The user needs an Excel formula to automatically categorize employee work schedules into specific shifts (e.g., Day, Night, or Mid shift) based on shift start times and durations.

How to Categorize Shifts by Day, Night, or Mid Shift in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating employee timesheets where shifts need to be classified based on specific time thresholds and durations.
Observed behavior: A standard logical test fails on overnight shifts because subtracting the start time from the end time across midnight yields a negative value, requiring functions like MOD to correctly calculate the duration.

Before you start

Ensure your start and end time columns are formatted as 'Time' in Excel, and that you have defined exact time thresholds for your Day, Night, and Mid shifts in separate reference cells.

Solution 1Recommended

Use Nested IF, AND, and MOD Functions to Categorize Shifts

By combining logical checks with the MOD function, you can accurately calculate shift durations and assign a shift name, even if the schedule crosses midnight.

To properly classify a shift, we must determine its duration and compare its start time against our defined business rules. The MOD function is essential here because it handles the negative time values that occur when an employee works past midnight.

Define Your Shift Thresholds

Set up absolute reference cells for your shift parameters in your worksheet. For example, enter the start time of the Day shift in cell H8, the Night shift start time in H10, and the standard shift duration threshold in D2.

Understand the MOD Calculation

Use the MOD function to handle shifts crossing midnight. The formula expression MOD(B2-A2, 1) calculates the total positive hours worked between a start time in cell A2 and an end time in cell B2.

Input the Nested IF Formula

In your target shift category column, enter the formula: =IF(AND(MOD(B2-A2,1)=$D$2,A2>=$H$10),"Night Shift", IF(AND(MOD(B2-A2,1)<=$D$2,A2<$H$8),"Day Shift", "Mid Shift")). You will need to customize the cell references and logical operators (like >= or <) to match your specific shift rules.

Apply Formula to All Rows

Press Enter to execute the formula. Click the categorized cell and drag the fill handle at the bottom-right corner down the column to classify all other employee shifts.

Use Nested IF, AND, and MOD Functions to Categorize Shifts

Locking Reference Cells: Remember to use absolute references (like $D$2 and $H$10) for your threshold rules so they remain fixed when you drag and copy the formula down your dataset.

Easily Categorize Employee Shifts with WPS Spreadsheet

WPS Spreadsheet fully supports Excel's advanced logical and time-calculation functions like IF, AND, and MOD, making it simple to manage timesheets and categorize complex work shifts seamlessly.

1. Open Your Timesheet: Launch WPS Spreadsheet and open your document containing the employee shift start and end times.
2. Input the Formula: Select the cell where you want the shift type to appear, and type your nested IF and MOD formula.
3. Fill Down the Column: Use the fill handle at the bottom-right of the cell to drag the formula down, automatically categorizing all shifts instantly.

Full support for advanced time, date, and logical formulas100% compatible with Microsoft Excel (.xlsx) formatsLightweight, fast, and completely free to use for daily tasksBuilt-in templates for employee scheduling and HR management

microsoft office alternative - wps office

Frequently Asked Questions

Why is the MOD function necessary for calculating night shifts?

When a shift starts before midnight (e.g., 10:00 PM) and ends after midnight (e.g., 6:00 AM), simply subtracting the start time from the end time results in a negative number, which Excel cannot process as time. Using MOD(End_Time - Start_Time, 1) forces the result into a correct positive time duration.

How do I correctly format my cells for time data?

Highlight the cells containing your schedule data, right-click, and select 'Format Cells'. Navigate to the 'Number' tab, select 'Time', and choose your preferred time format. Ensure both your raw data and your reference cells use the identical format.

Can I use the IFS function instead of nested IF statements for more than three shifts?

Yes. If you are using a modern spreadsheet application and have multiple shift types (e.g., Morning, Afternoon, Evening, Night), the IFS function allows you to list multiple logical conditions sequentially without deep nesting, making your formula much easier to read and maintain.

Excel & Data Problems