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
I just couldn't get the solution provided by Mike to work, as fascinating as it was; as such, I pieced together the following solution:
A hidden column which I labeled "Pull Forward," with the formula:
=ArrayFormula(If(C3:C<>"",B2:B999,""))
This brought the previous time into the same row for easy calculations, in the hours column:
=Arrayformula(If(C2:C="Out",B2:B-D2:D,""))
Not the sexiest answer, but it's reliably getting the job done.
Thanks,
CTO