Google Sheets: ArrayFormula Conditional Calculations across Rows

Question

struggling to find a way to perform a calculation taking a look at a previous row in an Arrayformula. Perhaps it isn't possible the way I'm attempting to do it?

Sample Columns:

Time/Date Conversion	In/Out	Hours
February 9, 2022 11:57PM	In
February 10, 2022 07:47AM	Out
February 10, 2022 11:28PM	In
February 11, 2022 08:40AM	Out

I'm able to use an arrayformula to determine if the row shows "Out"; and I know how to calculate the time difference between the date/times; but I can't seem to do the two in combo.

Resulting hours would appear in the Hours column when the In/Out row shows "Out."

BTW - Yes, this would be much easier if the In and the Out times were in the same row, but the software that auto-adds these to the spreadsheet doesn't do that (and doesn't even add them as a real date and time, so I had to use some funky regex to accomplish that. Good times!)

Thanks for your ideas, as always!

CTO

Answer 1

solution:

Try in C1 (format duration)

={"Hours";ARRAYFORMULA(if(B2:B="In",,if(A2:A="",,mmult(-1*(ROW(A2:A)=TRANSPOSE(ROW(A2:A)+1))+1*(ROW(A2:A)=TRANSPOSE(ROW(A2:A))),if(A2:A="",0,A2:A)))))}

reference:

mmult

explanation:

mmult between a matrix of -1/0/1 as follows and the data

=arrayformula(-1*(ROW(A2:A9)=TRANSPOSE(ROW(A2:A9)+1))+1*(ROW(A2:A9)=TRANSPOSE(ROW(A2:A9))))