Function Problems

How to Identify Day, Swing, and Graveyard Shifts with Excel Formulas

Tauseeq Magsi Oct 23, 2026 868 views

Question details

The user needs an Excel formula to categorize an employee's work shift (Days, Swings, or Graves) based on their specific departure time.

How to Identify Day, Swing, and Graveyard Shifts Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Automating employee shift classification for payroll or scheduling by evaluating departure times across three distinct daily periods.
Observed behavior: The shift must be correctly assigned based on the departure time, with special handling required for the Graveyard shift since it crosses midnight (22:00 to 06:30).

Before you start

Before applying the formula, verify that your employee departure times are formatted as standard Excel Time values (e.g., 14:30 or 2:30 PM) rather than plain text, as the TIME function relies on numerical time values to calculate correctly.

Solution 1Recommended

Use a Nested IF Formula with OR and TIME Functions

This is the most reliable method to classify times into distinct shift periods, properly handling overnight shifts that span across midnight.

To classify the shifts, you need a formula that evaluates the departure time against specific cutoff periods. The TIME(hour, minute, second) function is used to create reliable time thresholds.

The Graveyard shift crosses midnight (22:00 to 06:30), meaning the departure time could either be late at night (>= 22:00) or early in the morning (< 06:30). We use the OR function to capture both conditions.

Select the target cell

Click on the cell where you want the shift classification text (Days, Swings, or Graves) to appear, such as cell B1 next to your departure time in A1.

Enter the nested IF formula

Type the following formula into the formula bar: =IF(OR(A1<TIME(6,30,0),A1>=TIME(22,0,0)),"Graves",IF(A1<TIME(14,30,0),"Days","Swings"))

Execute the formula

Press the Enter key to apply the formula. The cell will now display the correct shift name based on the time in cell A1.

Apply to multiple rows

Hover over the bottom-right corner of the cell containing the formula until the cursor turns into a plus sign (+). Click and drag the fill handle down to apply the shift classification formula to the rest of your employee data.

Use a Nested IF Formula with OR and TIME Functions

Formula Logic Explained: The formula first checks if the time is before 06:30 OR after 22:00. If true, it assigns 'Graves'. If false, it moves to the next IF statement to check if the time is before 14:30. If true, it assigns 'Days'. If neither is true, it defaults to the remaining time slot, assigning 'Swings'.

Seamless Shift Management

Classify Employee Shifts Easily in WPS Spreadsheet

WPS Spreadsheet fully supports the IF, OR, and TIME functions used to classify work shifts. It is a powerful, free tool designed to help HR professionals and shift managers organize schedules and payroll effortlessly.

1. Open your schedule: Launch WPS Spreadsheet and open the document containing your employee departure times.
2. Input the classification formula: Select an empty cell next to the time entry, type the =IF(OR(TIME...)) formula exactly as you would in Excel, and press Enter.
3. Drag to fill: Double-click or drag the fill handle on the bottom-right corner of the cell to classify all shifts in your column automatically.

100% compatible with Microsoft Excel formulas, including nested IFs and TIME functions.Free and lightweight alternative for seamless employee schedule management.Intuitive user interface for quickly formatting time data and applying batch formulas.

Frequently Asked Questions

What should I do if my time data includes the date?

If your cell contains a combined date and time (e.g., '10/24/2023 15:00'), the standard TIME formula might fail because it evaluates the full date value. Use the MOD function to extract just the time. Replace 'A1' in your formula with 'MOD(A1,1)' so the formula ignores the date portion.

How do I change the shift cutoff times in the formula?

You can easily adjust the cutoff times by modifying the values inside the TIME(hour, minute, second) functions. For example, to change the Day shift start time to 7:00 AM instead of 6:30 AM, change TIME(6,30,0) to TIME(7,0,0) within the formula.

Why is the formula returning a #VALUE! error?

A #VALUE! error typically occurs if the cell being referenced contains text instead of a recognizable time. Select your time cells, go to the 'Home' tab, and ensure the Number Format is set to 'Time'. If the data was imported from another system, you may need to use the 'Text to Columns' feature under the Data tab to convert it.

Can I add a fourth shift to this classification?

Yes. You can add more shift classifications by nesting another IF condition inside the formula. For example, if you want to split the 'Days' shift into 'Morning' and 'Afternoon', you would replace 'Days' with another IF statement such as IF(A1<TIME(10,0,0),"Morning","Afternoon").

Excel & Data Problems