I am working on a Google sheet to automatically update attendance at trainings throughout the year. Attendees fill out a Google form which is dumped into a Google Sheet. Then, I need to be able to pull that data into a different sheet based on school name and last name and timestamp. I am a novice at all of this and am having a hard time figuring this out. Any help is greatly appreciated. Here is the link to the sheets. My formula is on the "ABC School" tab with some notes about how I would like it to act. And, another person tried something on another tab which did not update the way it should and pulled "#N/A" if the first date wasn't right and then stopped pulling for that person. Thanks for your help!


1 Answer 1

=IFERROR(IF(DATEDIF(E$1,FILTER('R1'!$A$2:$A,'R1'!$C$2:$C = $B2, VALUE('R1'!$A$2:$A) >= VALUE(E$1)),"D") = 0, "x",""))

There's a lot going on here, hopefully this explanation will suffice.

Firstly, we FILTER the R1 Sheet's entire A column, save for the header. We then have two conditions that must be met: The C column must be equal to the B2 (i.e. specifying the last name) and that the value of the A column must be greater than or equal to the value found in the first row (i.e. checking against the date). I had to wrap the date in a VALUE formula since it wasn't comparing correctly without it.

This leaves us with all the rows with the correct person. Notice that we are using >= instead of a simple equal sign, but this is necessary since the date value in the A column has a time component which makes gives them a greater value.

We then input that array we got from filtering into the second argument of DATEDIF or date difference. Using an array this way means the first value in the array will be considered. The first argument is the date we are looking at. It's in this order because the function returns an error if it's the other way around. Setting the third argument as 'D' tell the function to return how many days there are in between the two days.

With the difference, check if the value is 0, if it is return a x, and if there isn't then don't return anything.

Wrap the entire thing in IFERROR since FILTER returns an error if it didn't find anything that matches the search.

NOTE: this probably isn't the most efficient way of doing this since each cell has to have the formula above. Set it at the top most corner and then drag it to ensure that the cell references are correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.