Function Problems

Return the Date and Time of Minimum and Maximum Values in Excel

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Return the date and time associated with the minimum and maximum test results in an Excel worksheet.

Product
Microsoft Excel
Device & OS
not provided
Scenario
Test dates, times, and results are stored in separate columns, and the user wants to display the date and time beside each summary value.
Observed behavior
The desired output is the date and time from the row containing each minimum or maximum result.
Before you start

Confirm that column B contains Excel dates, column C contains Excel times, and column D contains numeric test results. The examples use rows 2–100; adjust all three ranges to cover the same rows in your table.

Solution 1Recommended

Use INDEX and MATCH to return the date and time

Find each extreme result in column D and combine the corresponding date and time from columns B and C.

These formulas return a numeric Excel date and time, so you can format the result or use it in other calculations.

1
Return the minimum result's date and time

Select the cell beside your minimum summary value and enter =INDEX($B$2:$B$100,MATCH(MIN($D$2:$D$100),$D$2:$D$100,0))+INDEX($C$2:$C$100,MATCH(MIN($D$2:$D$100),$D$2:$D$100,0)).

2
Return the maximum result's date and time

Select the cell beside your maximum summary value and enter =INDEX($B$2:$B$100,MATCH(MAX($D$2:$D$100),$D$2:$D$100,0))+INDEX($C$2:$C$100,MATCH(MAX($D$2:$D$100),$D$2:$D$100,0)).

3
Format the results

Select both formula cells, open Format Cells, and choose Number > Custom. Enter m/dd/yy hh:mm:ss AM/PM as the format and select OK.

Use INDEX and MATCH to return the date and time
Repeated results: If an extreme value occurs more than once, MATCH returns the first matching row. The formulas examine the full specified range, including filtered-out rows.
WPS Spreadsheet

Find the date and time of extreme results in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use INDEX and MATCH formulas to retrieve the date and time for each minimum or maximum result.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check that dates are in column B, times in column C, and results in column D.
  2. 2. Find the minimum: Select a cell beside the minimum value and enter =INDEX($B$2:$B$100,MATCH(MIN($D$2:$D$100),$D$2:$D$100,0))+INDEX($C$2:$C$100,MATCH(MIN($D$2:$D$100),$D$2:$D$100,0)). Adjust the ranges to match your data.
  3. 3. Find the maximum: Select a cell beside the maximum value and enter =INDEX($B$2:$B$100,MATCH(MAX($D$2:$D$100),$D$2:$D$100,0))+INDEX($C$2:$C$100,MATCH(MAX($D$2:$D$100),$D$2:$D$100,0)). Use the same row limits as in your minimum formula.
  4. 4. Display the date and time: Select the formula cells, open Format Cells, and choose a custom format such as m/dd/yy hh:mm:ss AM/PM.
Find the date and time associated with extreme test results.Work with Microsoft Excel XLSX and XLS files.Keep the returned date and time available for calculations.
QA img-9

Frequently Asked Questions

What happens if the minimum or maximum occurs more than once?

MATCH with an exact-match setting of 0 returns the first matching row in the specified result range. The formulas therefore display the date and time of that first occurrence.

Can I look up a minimum value that is already calculated in another cell?

Yes. Replace MIN($D$2:$D$100) in both MATCH functions with the cell containing the calculated minimum, such as F2. For the maximum formula, replace MAX($D$2:$D$100) with the cell containing the maximum.

Why does the formula show a number instead of a date and time?

Excel stores dates and times as numbers. Select the formula cell, open Format Cells, and apply a custom format such as m/dd/yy hh:mm:ss AM/PM.

Can I return the date and time as text instead?

Yes. After retrieving the date and time, use TEXT to format them. For example, =TEXT(B2,"m/dd/yy ")&TEXT(C2,"hh:mm:ss") combines the values in row 2 as text, but the result cannot be used directly as a numeric date and time.