How to Find the Latest Date and Time for Matching Records in WPS Spreadsheets
Question details
The user wants to identify or mark the row containing the latest date and time for each matching combination of values in columns C and D.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- You have a table where columns C and D define matching groups and column G contains date-and-time values, and you need the latest record for each group.
- Observed behavior
- The goal is to mark or return the latest date-time row for each C and D combination without manually scanning the table.
Confirm that columns C and D contain the group identifiers and column G contains true Excel date-time numbers. If the date-time values are stored as text, convert them first so functions such as MAX and LARGE compare them correctly.
Use a PivotTable to Show the Latest Date and Time by Group
Best when you need a summary table that displays the maximum date-time for each C and D combination.
Select the data range or click inside the table, then go to Insert > PivotTable.
In the Create PivotTable dialog, select New Worksheet or Existing Worksheet and click OK.
Drag the column C and column D fields to Rows, drag the column G date-time field to Values, then click the value field and select Value Field Settings > Max.
Use Power Query to Group Matching Records and Return the Latest Date-Time
Use this when the source data changes and you want a refreshable result table.
Mark the Latest Row for Each C and D Combination with a LARGE/IF Array Formula
Use this when you need to keep all source rows and place an X or marker on the row that has the latest date-time for each group.
Find and Mark the Latest Records in WPS Spreadsheets
Use WPS Spreadsheets to build a PivotTable, Power Query summary, or LARGE/IF array formula that returns or marks the latest date-time for each matching C and D combination.
- 1. Open your workbook: Open your workbook in WPS Spreadsheets and prepare columns C, D, and G with group values and true date-time numbers.
- 2. Create a summary or grouping: Select the data range, click Insert > PivotTable, and set the date-time field to summarize by Max; or use Data > From Table/Range for Power Query.
- 3. Mark rows with a formula: For row-by-row marking, enter the LARGE/IF formula in a helper column and fill it down to the last data row.
- 4. Refresh output when needed: After changing source data, refresh the PivotTable or Power Query result table by right-clicking it and selecting Refresh.
Frequently Asked Questions
Can the LARGE/IF formula mark the latest row automatically in older Excel versions?
Yes. After entering the formula, press Ctrl+Shift+Enter instead of Enter in older array-formula versions. Excel or WPS Spreadsheets will display the formula wrapped in braces and calculate it correctly.
What should I do if the date and time are stored in separate columns?
Create a helper column that combines them into a single Excel date-time value, for example =C2+D2, and then point the PivotTable, Power Query, or LARGE/IF formula to that helper column.
How do I refresh a Power Query result table after editing the source data?
Right-click a cell in the result table and choose Refresh, or click Data > Refresh All to update all query results.
Why does the formula return a wrong result when the date-time column is text?
MAX and LARGE require numeric date-time values. Convert text values with Text to Columns, DATEVALUE/TIMEVALUE, or by correcting the cell format and re-entering the values.
Can a PivotTable show the full source row for the latest record?
A PivotTable summarizes the maximum date-time per group rather than marking the full source row. To keep the complete row, use Power Query or the LARGE/IF helper-column formula.
