Forum Discussion
Excel Formula Help
I Need an excel formula to get total time spent in an account based on date, user, account number.
example below of my table data
| time | Account | User | Date |
| 2:13 PM | 123 | A7 | 8/1/23 |
| 2:13 PM | 123 | A7 | 8/1/23 |
| 3:13 PM | 123 | A7 | 8/1/23 |
| 1:00 AM | 456 | A1 | 8/5/23 |
| 1:52 AM | 456 | A1 | 8/5/23 |
| 7:32 AM | 123 | A4 | 8/9/23 |
| 7:35 AM | 123 | A4 | 8/9/23 |
| 12:21 PM | 123 | A4 | 8/9/23 |
3 Replies
=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)))
With Office 365 or Excel for the web you can apply this formula.
=INDEX($A$2:$A$9,LARGE(IF(($B$2:$B$9=B2)*($C$2:$C$9=C2)*($D$2:$D$9=D2),ROW($A$1:$A$8)),1))-INDEX($A$2:$A$9,SMALL(IF(($B$2:$B$9=B2)*($C$2:$C$9=C2)*($D$2:$D$9=D2),ROW($A$1:$A$8)),1))
An alternative for other versions of Excel could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. This formula is in cell E2 and copied down.
- In the "Time" column, is this the beginning of time? If so, where does the time end?
If it is the total time, why is it marked PM and AM?NikolinoDE the issue with the data report is it pulls start and end time in that one column under time.
I have been trying to figure out how to get the end time in another column quickly because of data can have have multiple times listed and the actual report I have is massive and would take forever to do it manually.
