Find the Earliest Positive Test or Symptom Date in Excel
Question details
Find the earliest symptom-onset date or test date associated with a positive result.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains a symptom date and multiple test dates paired with test results.
- Observed behavior
- The desired result is the earliest available symptom date or date of a positive test, excluding blank dates and non-positive test results.
Confirm that AB1 contains the symptom date and that M1/O1, P1/R1, S1/U1, and V1/X1 are the test date/result pairs. Dates should be stored as Excel date values, not text, and positive results should read Positive.
Use LET to return the earliest eligible date
Use this formula when you want to compare the symptom date with positive test dates and return a blank if none qualify.
The formula substitutes a distant sentinel date for ineligible entries, then uses MIN to find the earliest remaining date. ISNUMBER excludes blank cells and text, but does not verify that a numeric value is a meaningful date.
LET is available in Excel 2021 and later and Microsoft 365. The sentinel date itself, December 31, 9999, is treated as having no eligible result.
Select an empty result cell and enter =LET(d,DATE(9999,12,31),earliest,MIN(IF(ISNUMBER(AB1),AB1,d),IF(AND(O1="Positive",ISNUMBER(M1)),M1,d),IF(AND(R1="Positive",ISNUMBER(P1)),P1,d),IF(AND(U1="Positive",ISNUMBER(S1)),S1,d),IF(AND(X1="Positive",ISNUMBER(V1)),V1,d)),IF(earliest=d,"",earliest)).
Select the result cell, then choose Home > Number Format > Short Date or another date format.
Drag the result cell's fill handle down to calculate the earliest eligible date for each row.

Find the earliest eligible date in WPS Spreadsheets
Use helper cells in WPS Spreadsheets to select the symptom date and positive test dates, then calculate the earliest value without relying on LET.
- 1. Open the worksheet: Open the Excel file in WPS Spreadsheets and confirm that AB1 is the symptom date and M1/O1, P1/R1, S1/U1, and V1/X1 are the test date/result pairs.
- 2. Add the symptom helper: In an unused cell such as AC1, enter =IF(ISNUMBER(AB1),AB1,"").
- 3. Add the test helpers: Enter =IF(AND(O1="Positive",ISNUMBER(M1)),M1,"") in AD1, =IF(AND(R1="Positive",ISNUMBER(P1)),P1,"") in AE1, =IF(AND(U1="Positive",ISNUMBER(S1)),S1,"") in AF1, and =IF(AND(X1="Positive",ISNUMBER(V1)),V1,"") in AG1.
- 4. Calculate and format the result: In AH1, enter =IF(COUNT(AC1:AG1)=0,"",MIN(AC1:AG1)), then use the Home tab's number format menu to format AH1 as a date.
- 5. Calculate other rows: Select AC1:AH1 and drag the fill handle down to copy the helper formulas and result to additional rows.

Frequently Asked Questions
What happens if no symptom date or positive test date is available?
The Excel formula returns a blank when all dates are missing or all tests are ineligible. The WPS helper-cell formula also returns a blank when COUNT finds no numeric dates.
Why does the result show a number instead of a date?
Excel and WPS store dates as serial numbers. Select the result cell and use the Home tab's number format menu to choose a date format.
What if a positive test has no date?
The formulas exclude that test because ISNUMBER checks that its date cell contains a numeric value. Enter a valid date in the corresponding test date cell to include it.
Can I require a symptom date before showing a result?
Yes. In Excel, wrap the LET formula in =IF(AB1="","",your_LET_formula), replacing the placeholder with the LET expression without its leading equals sign.




