Excel Formula Help

Copper Contributor

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

Re: Excel Formula Help

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?

Re: Excel Formula Help

@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.

Re: Excel Formula Help

=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.