How to Check If a Time Is in Either of Two Ranges in WPS Office Excel
Question details
The user needs a WPS Office Spreadsheets formula that checks whether a recorded time falls within either of two specific time ranges and returns Yes or No accordingly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A speed-study worksheet records times and must classify each record as inside or outside the morning 7:00-8:30 range or afternoon 2:26-3:26 range.
- Observed behavior
- The worksheet should show Yes for times inside either specified range and No for all other times.
Make sure the boundary cells and recorded time cells are formatted as valid time values. If times are stored as text, the AND/OR comparisons may return unexpected results.
Use an IF, OR, and AND Formula with Absolute References
Use this when the four range boundaries are stored in worksheet cells and you want a reliable Yes/No result that can be filled down without changing the boundary references.
The formula uses AND to test the morning boundaries and afternoon boundaries separately, then uses OR to return TRUE when either range matches. Absolute references such as $A$2 keep the boundary cells fixed when the formula is copied.
In a WPS Office Spreadsheets worksheet, type 7:00 AM in cell A2, 8:30 AM in cell A3, 2:26 PM in cell A4, and 3:26 PM in cell A5. Select these cells, press Ctrl+1, and set the format to Time.
Click the first cell where the result should appear, for example E2, next to the first recorded time in cell D2.
Type =IF(OR(AND(D2>=$A$2,D2<=$A$3),AND(D2>=$A$4,D2<=$A$5)),"Yes","No") and press Enter.
Drag the fill handle from E2 down through all recorded time cells to apply the same logic to every row.
Use TIME Functions When Boundaries Are Hard-Coded in the Formula
Use this when you do not want helper boundary cells and prefer to enter the morning and afternoon times directly in the formula.
Check Time Ranges in WPS Office Spreadsheets with One Formula
Open your speed-study workbook in WPS Office, build the IF/OR/AND formula, and fill it down to automatically mark every recorded time as Yes or No based on the two time ranges.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open the speed-study worksheet, and enable editing if needed.
- 2. Prepare the time boundary cells: Enter 7:00 AM, 8:30 AM, 2:26 PM, and 3:26 PM into A2:A5 and format them as Time values.
- 3. Enter the result formula: In E2, type =IF(OR(AND(D2>=$A$2,D2<=$A$3),AND(D2>=$A$4,D2<=$A$5)),"Yes","No") and press Enter.
- 4. Fill the formula down: Drag the fill handle down to apply the formula to all recorded times in column D.
- 5. Verify Yes/No outputs: Confirm that times within the morning or afternoon ranges show Yes and times outside both ranges show No.
Frequently Asked Questions
Why does the formula return No for a time that looks like it is inside the range?
The most common cause is text-formatted time. Select the time cells, press Ctrl+1, choose Time, and re-enter the values if necessary. The formula only compares true time values reliably.
Can I add a third time range to the same formula?
Yes. Add another AND(...) block inside the OR function, for example =IF(OR(AND(D2>=$A$2,D2<=$A$3),AND(D2>=$A$4,D2<=$A$5),AND(D2>=$A$6,D2<=$A$7)),"Yes","No").
Do the boundary times have to be in separate cells?
No. Use the TIME-function version if you prefer to hard-code the times, but keeping boundaries in cells makes them easier to update.
What does the absolute reference $A$2 do?
It locks the boundary cell reference so when you fill the formula down, every row still compares against the original time range cells instead of shifting to A3, A4, and so on.
Will this time-range formula work in Microsoft Excel?
Yes. The IF, OR, AND, and TIME functions use standard syntax, so the same workbook and formula work in both WPS Office Spreadsheets and Microsoft Excel.
