Function Problems

WPS Spreadsheet: Test Whether a Name Falls Between Two Dates

Rana GarciaRana Garcia Nov 12, 2026 868 views

Question details

The user needs to determine whether a name appears within the interval between surrounding date entries in a single spreadsheet column.

Product
WPS Spreadsheet
Device & OS
not provided
Scenario
Dates separate groups of names in one column, and the user wants to test a target name against the latest date above and the next date below.
Observed behavior
The name and date rows are mixed in one column, so the surrounding start and end dates must be identified before the interval test can return TRUE or FALSE.
Before you start

Confirm which cells contain dates and names, identify the target-name cell, and decide whether the date boundaries should be exclusive or inclusive. The example below assumes data is in A1:A9 and the target name is entered in F1.

Solution 1Recommended

Use an AND formula with separate start and end date cells

Use this approach when dates and names are stored in normalized columns and the interval boundaries are known.

For a normalized layout, compare the current date with the interval start and end dates using AND. Use greater-than and less-than for exclusive boundaries, or change them to greater-than-or-equal and less-than-or-equal when the boundary dates should count.

1
Set up the interval columns

Place the name in one column, the current date in another, the start date in a third column, and the end date in a fourth column. For example, use B2 for the name, C2 for CurrentDate, D2 for StartDate, and E2 for EndDate.

2
Enter the interval test

Select the result cell and enter =AND(C2>D2,C2<E2,B2=$F$1), then press Enter. This returns TRUE only when the name matches F1 and the date is strictly between the start and end dates.

3
Include the boundary dates if needed

For an inclusive interval, replace the formula with =AND(C2>=D2,C2<=E2,B2=$F$1). Use the fill handle to copy the formula down the result column.

Use an AND formula with separate start and end date cells
Boundary choice matters: Use > and < when the surrounding dates are excluded. Use >= and <= when a name on either boundary date should be included.

Check Date Intervals and Name Matches in WPS Spreadsheet

WPS Spreadsheet lets you test names against date intervals with AND, IF, INDEX, and ISNUMBER formulas. You can use a simple normalized layout or fill a helper formula through a mixed date-and-name column.

  1. 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the dates and names.
  2. 2. Identify the data range: Review the mixed column and note the first and last cells, such as A1:A9, along with the cell where the target name will be entered.
  3. 3. Enter the formula: Select the result cell, enter the appropriate AND formula for normalized data or the INDEX-based formula for mixed data, and press Enter.
  4. 4. Fill and verify results: Drag the fill handle down the result column, then change the target name cell to confirm that the TRUE or FALSE result updates.
Check whether a name falls between two dates without using IFSUse formulas such as AND, IF, INDEX, and ISNUMBER in a familiar spreadsheet interfaceFill interval tests down a column and update results dynamically when the target name changesOpen and save Microsoft Excel XLSX workbooks with compatible spreadsheet formatting
microsoft office alternative - wps office

Frequently Asked Questions

Can I test the name between dates without using IFS?

Yes. Use AND to compare the current date with the start and end dates, together with a name comparison such as B2=$F$1. IFS is not required for this interval test.

How do I check a name when dates and names are in the same column?

Use helper formulas or the INDEX-based formula in the second solution. It locates the latest numeric date above the name and the next numeric date below it, then compares the name and interval.

How can I include the exact start or end date?

Change the exclusive comparisons > and < to inclusive comparisons >= and <=. For example, use =AND(C2>=D2,C2<=E2,B2=$F$1) in a normalized layout.

Why does the formula return a blank row?

The IF condition intentionally returns a blank when the current cell does not match the target name in F1. Only matching name rows display the interval result.

What should I change if my data has more than nine rows?

Replace every A1:A9 or A2:A9 reference with your actual mixed data range, such as A1:A200, and then fill the result formula through the corresponding rows.