How to Display the Oldest Value for Each Day in a WPS Office PivotTable
Question details
The user needs a PivotTable that identifies the earliest transaction from date-time values and returns the amount associated with that earliest timestamp for each day.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has transactional data with date-time values and needs a PivotTable showing the amount from the earliest transaction for each day.
- Observed behavior
- Standard PivotTable day grouping shows daily summaries, but it does not automatically return the amount linked to the oldest timestamp for each day.
Open your source data in WPS Spreadsheets and confirm that the date column is stored as a real date/time value, not as text. Add a helper column that extracts the day with =INT(A2) so it can be used as the grouping key.
Return the Earliest Amount with a Helper Column and PivotTable Filter
Use this method when you need the actual amount from the oldest transaction for each day and want the result to update with the source data.
A helper flag marks the row whose time value is the minimum for its day. Filtering the PivotTable by that flag keeps only the oldest record per day, while the Day field groups the output.
In WPS Spreadsheets, open your source sheet and insert two columns next to the transaction data: one named Day and one named Earliest flag.
In the Day cell C2, enter =INT(A2) and fill it down to convert the date-time in column A into a date-only value.
In the Earliest flag cell D2, enter =IF(MOD(A2,1)=MINIFS(MOD($A$2:$A$100,1),$C$2:$C$100,C2),"Earliest","") and fill it down. Adjust the row range to match your data.
Select the data including helper columns, click Insert > PivotTable, and choose New Worksheet. Drag Day to Rows, Amount to Values, and Earliest flag to Filters.
In the PivotTable filter for Earliest flag, select Earliest and click OK. The Amount column now shows the value associated with the earliest timestamp of each day.
Group Date-Time by Day and Show the Minimum Time Only
Use this method when you only need to see the oldest time for each day, not the related amount.
Display the Oldest Daily Value in WPS Office Spreadsheets
WPS Spreadsheets can return the amount from the earliest transaction for each day by combining a helper flag with a PivotTable filter, without changing your source file format.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx file containing the date-time and amount columns.
- 2. Add the helper flags: Create Day and Earliest flag columns, then fill them with =INT(A2) and the MINIFS formula to identify the oldest row per day.
- 3. Insert a PivotTable: Select the range including the helper columns, click Insert > PivotTable, and choose where to place the report.
- 4. Build and filter the report: Drag Day to Rows, Amount to Values, and Earliest flag to Filters; set the filter to Earliest to see the daily oldest amount.
Frequently Asked Questions
Why does my PivotTable show a daily sum instead of the oldest value?
By default, PivotTables summarize numeric fields with Sum or Count. To show the oldest value, add a helper flag for the earliest timestamp and filter on that flag, or change the value field to Min if you only need the oldest time.
Can a PivotTable return the amount from the earliest timestamp without a helper column?
A standard PivotTable cannot directly look up the amount for the minimum time in a date-time field. Use a helper column with INT and MINIFS, or use a Power Pivot measure in an environment that supports it.
How do I group date-time values by day when the time is included?
Right-click the date-time field in the Rows area, choose Group, select Days, and clear Months and Years. This groups the field by date while keeping the original time available in the source data.
What formula can flag the oldest transaction for each day?
Use =IF(MOD(A2,1)=MINIFS(MOD($A$2:$A$100,1),$C$2:$C$100,C2),"Earliest","") after creating a Day column with =INT(A2). The Day column must match the MINIFS criteria range.
