Calculate Total Time by Account, User, and Date in Excel Using WPS Office
Question details
The user needs to group repeated time entries by account, user, and date, then calculate the difference between the earliest and latest recorded time for each group.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A table contains Time, Account, User, and Date columns with repeated entries; the user wants a summary table showing one total elapsed time per unique account-user-date group.
- Observed behavior
- The raw data has multiple rows per account-user-date group, so the workbook must identify each group and subtract the earliest time from the latest time.
Before writing formulas, confirm whether the Time column stores clock times or elapsed durations and make sure all time cells use a consistent time or duration format. If times can cross midnight, include the full date-time value or adjust the formula to avoid negative totals.
Use a dynamic array formula to group entries and subtract earliest from latest
Best for WPS Office Spreadsheet and Microsoft 365/Excel for the web users who want one spill formula that creates the unique account-user-date summary and calculates total time automatically.
This formula assumes column A contains the time values and columns B, C, and D contain Account, User, and Date. The UNIQUE function generates each group, and the LAMBDA/BYROW/FILTER part returns the last time minus the first time for that group.
Open the workbook in WPS Office Spreadsheet and click an empty cell where the summary table should start, such as F1.
Type or paste =VSTACK({"Account","User","Date","Total time"},HSTACK(UNIQUE(B2:D9),DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(B2:D9))),LAMBDA(u,v,VSTACK(u,TAKE(FILTER(A2:A9,BYROW(B2:D9=INDEX(UNIQUE(B2:D9),v,),LAMBDA(x,AND(UNIQUE(TOCOL(x))=TRUE,COUNTA(UNIQUE(TOCOL(x)))=1)))),-1)-TAKE(FILTER(A2:A9,BYROW(B2:D9=INDEX(UNIQUE(B2:D9),v,),LAMBDA(x,AND(UNIQUE(TOCOL(x))=TRUE,COUNTA(UNIQUE(TOCOL(x)))=1)))),1)))),1))) and adjust B2:D9 and A2:A9 to match your actual data range.
Press Enter. Select the Total time column, right-click, choose Format Cells, and apply a custom time format such as [h]:mm so elapsed time displays correctly.
Use a MAX MIN array formula with unique criteria for older versions
Use this when you already have or can manually create a unique Account, User, and Date table and need a non-dynamic array calculation.
Calculate Total Time by Account, User, and Date in WPS Office Spreadsheet
WPS Office Spreadsheet supports dynamic array functions including UNIQUE, FILTER, BYROW, LAMBDA, VSTACK, and HSTACK, so you can build the same grouped time calculation directly in WPS Office. The workbook remains fully compatible with Microsoft Excel .xlsx files and familiar Excel formulas.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office and open the Excel workbook containing the Time, Account, User, and Date columns.
- 2. Select an empty summary area: Click an empty worksheet or an empty range where the grouped summary table and total time column can spill.
- 3. Enter the dynamic array formula: Paste the UNIQUE/HSTACK/REDUCE formula shown in Solution 1 and replace the example ranges A2:A9 and B2:D9 with your actual data range.
- 4. Adjust formatting and save: Apply a custom time format such as [h]:mm to the total time results, then save the workbook as .xlsx to keep Excel compatibility.
Frequently Asked Questions
Does WPS Office support dynamic array functions like UNIQUE and LAMBDA?
Yes, recent WPS Office Spreadsheet versions support dynamic array functions such as UNIQUE, FILTER, VSTACK, HSTACK, BYROW, and LAMBDA, so the grouped time formula can be used directly. If the formula returns #NAME?, update WPS Office.
What should I check before calculating total time?
Confirm whether the Time column contains clock times or elapsed durations, and use a consistent time format. If times can cross midnight, include the full date-time value or use a custom formula to avoid negative totals.
How do I adapt the formula to my actual data range?
Replace A2:A9 with the actual time range and B2:D9 with the actual Account, User, and Date columns. Keep the header row outside the data range and update all range references consistently.
Can I calculate total time without dynamic arrays?
Yes, create a unique Account, User, and Date criteria table, then use an array formula such as =MAX(IF(($B$2:$B$9=$G2)*($C$2:$C$9=$H2)*($D$2:$D$9=$I2),$A$2:$A$9))-MIN(IF(($B$2:$B$9=$G2)*($C$2:$C$9=$H2)*($D$2:$D$9=$I2),$A$2:$A$9)) and press Ctrl+Shift+Enter if required.
Why does the result show as AM/PM or decimals?
Time subtraction returns a decimal fraction of a day. Apply a custom time format such as [h]:mm to display elapsed hours and minutes, especially for totals over 24 hours.
