Skip to content

Instantly share code, notes, and snippets.

@sblom

sblom/TotalHours.excel

Last active October 30, 2023 22:17
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sblom/2a29b8bf59bdb74700b229b3692fc53e to your computer and use it in GitHub Desktop.
Save sblom/2a29b8bf59bdb74700b229b3692fc53e to your computer and use it in GitHub Desktop.
Download ZIP
Native Excel array function to take a horizontal array of clock-in/clock-out times and return total hours
Raw
TotalHours.excel
=LET(MakeAcc,LAMBDA(item1,item2,MAKEARRAY(2,1,LAMBDA(row,col,IF(row=1,item1,item2)))),
LAMBDA(arr,INDEX(REDUCE({0;-1},FILTER(arr,MAP(arr,LAMBDA(x,ISNUMBER(x))),{0}),LAMBDA(accum,time,
LET(total,INDEX(accum,1),start,INDEX(accum,2),
IF(start<>-1,
MakeAcc(total+(time-start)*24,-1),
MakeAcc(total,time)
)
))),1)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment