How to use an ArrayFormula containing a partial range

Question

I have a Google Sheet that is populated by a linked Google Form. I've added some extra columns to calculate extra values for each row that gets added. I want these columns to be automatically calculated when new rows are populated, so I'd like to use ARRAYFORMULA().

Several of my simple formulas are working fine, but I can't get the ArrayFormula syntax correct for a formula which uses FILTER() to calculate the maximum value in a column for all the cells above the current cell.

Timestamp   Name  In or out?    Shift start time
14:45:59    N     Clock-in     (start)
14:51:02    T     Clock-in     (start)
14:51:46    N     Clock-out    2:45:59 PM
15:03:51    J     Clock-in     (start)

Single-cell version (for calculating D6):

=if(C6="Clock-out",Max(FILTER(A$2:A6,A$2:A6<A6,B$2:B6=B6,C$2:C6="Clock-in")),"(start)")

My (incorrect) attempt at arrayFormula for calculating cells D2 to D12:

=ArrayFormula(if(C2:C12="Clock-out",Max(FILTER(A$2:{A2:A12},A$2:{A2:A12}<{A2:A12},B$2:{B2:B12}={B2:B12},C$2:{C2:C12}="Clock-in")),"(start)"))

How can I tell the arrayFormula to increment the end cell of the filter range each time, but keep the starting row the same?

I'm also open to other approaches besides FILTER (I tried Vlookup too, but didn't have any more luck). What I ultimately seek is a formula that finds the latest "start" time, with the same name label as the current cell, occurring before the current cell's timestamp.

Simplified example spreadsheet: https://docs.google.com/spreadsheets/d/1bUvlDvNY3BaXQZwfCe4XyKw-jD5M3ELEf_yM9QJTglQ/edit?usp=sharing

Answer 1

Try this:

In your linked sample sheet, delete all of column D to prepare for the array formula.

Next, with the entire Column D selected, set the format: Format > Number > Time

Then place this formula in D1:

=ArrayFormula(IF(ROW(A:A)=1,"Shift start time",IF(A:A="","",IF(C:C="Clock-in","(start)",VLOOKUP(B:B&"Clock-in"&A:A-TIME(0,0,1),SORT({B:B&C:C&A:A,A:A},1,1),2,TRUE)))))

HOW IT WORKS

Of course, it's an array formula, since we wrapped it in ...

=ArrayFormula( )

First we check to see if we are in row 1. If so, we place the header there:

IF(ROW(A:A)=1,"Shift start time"

Then we check to see if the cell in Column A for the given row is blank. If so, the formula will also just leave a blank:

IF(A:A="",""

Next, if Column C contains "Clock-in" the formula will place "(start)":

`IF(C:C="Clock-in","(start)"

Nothing earth shattering thus far.

Finally, we want to create a virtual array in memory, and then search it with VLOOKUP.

The virtual search range will be "Frankensteined" together by concatenating existing pieces with one modification, and then sorting those concatenations:

SORT({B:B&C:C&A:A,A:A},1,1)

Curly brackets are another way to create an array. This virtual array will only have two columns:

1. The first column will be made up of single strings formed by joining NAME&"Check-in"&Timestamp. Each will wind up looking something like this in memory:

N43416.6152662037Clock-in

2. The second column of the virtual array will be just the Timestamp.

3. We sort this in ascending order using SORT on the first column. So in memory, those long strings we're forming will be in order by name and timestamp.

Now we're going to do a VLOOKUP on that sorted range. But we are going to create another concatenation to search for in each row: B:B&"Clock-in"&A:A-TIME(0,0,1)

Notice that everything is the same as the virtual range we formed except that we've subtracted 1 second from the Timestamp column.

Why?

Well, by sorting the virtual range, we can use TRUE as the last parameter of the VLOOKUP. This means that if VLOOKUP can't find an exact match, it will return the closest match that is less than our search. That's the key. By subtracting that one second, we force the VLOOKUP to find the closest match before our current time that also has a name match and says "Check-in."

Virtually, the VLOOKUP will get to the section of our Frankenstein range that has this person's name first. In that block of names, it will search for a listing with "Check-in" next and that has a time of exactly one second earlier than the "Check-out" time for each row. Of course, it won't find that, so it will back up and give you the last "Check-in" timestamp, because that would have been the last alpha-numeric listing that didn't go over the time-minus-one-second in that section.

Answer 2

I've solved a similar problem in a different way, which I will describe below. Hopefully this is useful for you:

My 'input' spreadsheet contain, similar to yours, a date, a check-in/check-out field and a username field. Instead of doing calculations on the input sheet, I've created a query that calculates for every user and for every day, the earliest check-in and the latest checkout.

The query's SQL is the following:

=query(query(Sheet1!B:E, "select D, min(E), max(E) group by D pivot C,B ", 1),"select Col1, Col3, Col10, Col4, Col11")

and an example spreadsheet can be found here:

https://docs.google.com/spreadsheets/d/11G3OWI7AplLiG8hR1oHwj6CaoAhXBU9l7sGniEyHi6k