How to Check Shift Allowance Eligibility in Excel Using Formulas
Question details
The user needs an Excel formula to determine if a worker qualifies for a shift allowance based on specific start and end time conditions.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating employee payroll or shift allowances based on specific night shift operating hours.
- Observed behavior
- The goal is to evaluate specific time constraints (e.g., start time is at or after 9:00 PM or before 6:00 AM, and end time is at or after 11:00 PM or before 6:00 AM) and accurately output whether the shift meets the allowance criteria.
Ensure your start and end times are formatted as valid Time values in Excel (e.g., hh:mm AM/PM) before applying the formulas.
Use AND/OR Formulas for Strict Shift Eligibility (Both Conditions Required)
Apply this formula when the worker must meet both the start time AND the end time conditions simultaneously to qualify for the allowance.
This method uses the TIME function to convert specific hours (21:00 for 9 PM, 6:00 for 6 AM, etc.) into a format Excel can evaluate. The logical AND and OR functions check if the logged hours fall within your required allowance window.
Enter the employee start times in column B and the end times in column C.
Click on the cell where you want to display the eligibility result, such as cell D2.
Type the following formula: =AND(OR(B2>=TIME(21,0,0),B2<TIME(6,0,0)),OR(C2>=TIME(23,0,0),C2<TIME(6,0,0)))
Press Enter to see the result (TRUE or FALSE). Click and drag the fill handle at the bottom-right corner of the cell to apply the formula to the remaining rows.

Use the OR Formula for Flexible Shift Eligibility (Either Condition Sufficient)
Apply this formula if the worker qualifies for the allowance by meeting EITHER the start time condition OR the end time condition.
Calculate Shift Allowances with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas like AND, OR, and TIME. You can quickly set up employee timecards, evaluate complex shift allowances, and process payroll with its intuitive interface.
- 1. Open a Spreadsheet: Launch WPS Office and open your existing timecard file or create a new Spreadsheet document.
- 2. Format Time Cells: Highlight your shift time columns, right-click, select 'Format Cells', and apply a valid Time format.
- 3. Paste the Logical Formula: Select the target allowance column and paste the AND/OR TIME formula provided in this guide.
- 4. Calculate Automatically: Press Enter and drag the fill handle to instantly calculate the allowance eligibility for all employees.

Frequently Asked Questions
How can I return 'Yes' or 'No' instead of TRUE or FALSE for shift eligibility?
You can wrap your logical formula inside an IF function. For example, use: =IF(AND(OR(B2>=TIME(21,0,0),B2<TIME(6,0,0)),OR(C2>=TIME(23,0,0),C2<TIME(6,0,0))), "Yes", "No").
Why is my TIME formula returning an error or incorrect result?
Ensure that the time values in your referenced cells (e.g., B2, C2) are recognized as valid time formats in Excel, not just plain text. You can fix this by selecting the cells, right-clicking, choosing 'Format Cells', and applying the 'Time' format.
How does the TIME function work in Excel?
The TIME function creates a valid decimal time value using three arguments: hour, minute, and second. For example, TIME(21,0,0) represents 21 hours (9:00 PM), 0 minutes, and 0 seconds.




