How to Find the Maximum Value Between Two Date-Time Boundaries in WPS Spreadsheets
Question details
The user needs a formula that returns the maximum value when criteria include both date and time components, such as between 12/02/2023 20:00 and 12/02/2023 20:30.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a dataset with date-time entries and wants to find the highest associated value within a narrow date-time range, not only by date.
- Observed behavior
- A date-only criteria formula works, but when time components are added the formula returns no match or an incorrect maximum because the time portion is ignored.
Check that your date-time column contains real date-time serial values instead of text, and place the start and end boundary values in separate cells you can reference.
Use MAXIFS with Inclusive Date-Time Criteria
Recommended for current WPS Spreadsheets versions because it handles date-time boundaries directly without array entry.
MAXIFS evaluates cells based on numeric serial values, so full date-time criteria work when boundaries are stored as real date-time values.
Click an empty cell where the maximum value should be returned.
Type =MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2) and press Enter. This returns the largest value in B2:B100 where the date-time in A2:A100 is between D2 and E2 inclusive.
Confirm the result matches the highest value inside the date-time range. If it is zero or blank, verify that the criteria cells contain full date-time serial values.
Use a MAX and IF Array Formula for Older Spreadsheet Versions
Use this legacy array formula when MAXIFS is not available or when you open an older .xls workbook that does not recognize MAXIFS.
Find Maximum Date-Time Values in WPS Spreadsheets
WPS Spreadsheets supports the same MAXIFS and MAX(IF(...)) formulas used in Excel, so you can open your workbook, apply the inclusive date-time criteria, and get the correct maximum without switching tools.
- 1. Open the workbook: Launch WPS Spreadsheets and open the Excel file containing the date-time data.
- 2. Select the result cell: Click an empty cell where you want the maximum value to appear.
- 3. Enter the formula: Type =MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2) or the MAX(IF(...)) array formula depending on your version.
- 4. Press Enter: Press Enter, or Ctrl+Shift+Enter for legacy array formulas, to complete the calculation.
- 5. Save the file: Save the workbook in .xlsx format to preserve compatibility with Excel and WPS Spreadsheets.
Frequently Asked Questions
Why does my MAXIFS formula work for dates but not when I add times?
Because date-time values are serial numbers with a decimal time portion. When criteria cells contain only dates, the time is midnight, so a date-time range that starts later in the day must include the time. Store full date-time values in the criteria cells and use MAXIFS with ">=" and "<=" comparisons.
Does WPS Spreadsheets support MAXIFS in .xlsx files?
Yes, current WPS Spreadsheets versions support MAXIFS in .xlsx and .xlsm workbooks. If the function is not recognized, your file may be in an older compatibility mode or your version may need an update.
Do I need to press Ctrl+Shift+Enter for the MAXIFS formula?
No. MAXIFS is a normal function and you press Enter. The legacy MAX and IF array formula usually requires Ctrl+Shift+Enter unless your version treats it as a dynamic array.
How can I tell whether my date-time cells are real values or text?
Select the cell and look at the formula bar. A real date-time value displays as a date/time serial or in a date-time number format, while text is enclosed in quotation marks or has an apostrophe. You can also use =ISNUMBER(A2) and check for TRUE.
Why does my array formula return 0 or #VALUE!?
Usually the formula was entered only with Enter instead of Ctrl+Shift+Enter in versions that require legacy array entry, or one of the ranges has different sizes. Re-enter it with Ctrl+Shift+Enter and make all ranges the same length.
