Formula Errors

Find the Earliest Positive Test or Symptom Date in Excel

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Find the earliest symptom-onset date or test date associated with a positive result.

Find the Earliest Positive Test or Symptom Date in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

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)).

2
Format the result as a date

Select the result cell, then choose Home > Number Format > Short Date or another date format.

3
Apply it to other rows

Drag the result cell's fill handle down to calculate the earliest eligible date for each row.

Use LET to return the earliest eligible date
Require a symptom date: If the result must be blank whenever AB1 is blank, wrap the formula as =IF(AB1="","",your_LET_formula). Replace your_LET_formula with the LET expression without its leading equals sign.
Calculate dates in WPS Spreadsheets

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. 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. 2. Add the symptom helper: In an unused cell such as AC1, enter =IF(ISNUMBER(AB1),AB1,"").
  3. 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. 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. 5. Calculate other rows: Select AC1:AH1 and drag the fill handle down to copy the helper formulas and result to additional rows.
Compare symptom-onset and positive test dates in one resultOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and date formattingWork with a free, lightweight office suite
microsoft office alternative - wps office

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.