I've got a gSheet that is fed from form submission. Throughout the day, I need to calculate the duration automatically; subtracting the most recent timestamp from the previous timestamp of the same user in column E. Is this possible?
Sheet: https://docs.google.com/spreadsheets/d/1_gbM2TVpUDrGU0B0_s1_aiH2r-9l6TUPxwlzo2SW338/edit#gid=0
Use this formula
=IF(A2="",, SORTN(IF(
COUNTIF(B$2:B2, "="&B2)>1=TRUE,
SORTN(FILTER(A$2:A2,B$2:B2=B2 ), 2, , 1, 0), A2),1,,1,1))
Use the formula in E2
=IF(B2="", ,IF(XLOOKUP($B2,$B$1:$B1,$A$1:$A1,"-",0,-1)="-","First time worker",$A2-XLOOKUP($B2,$B$1:$B1,$A$1:$A1,,0,-1)))
And drag it down till the end of the sheet