NEED HELP Attendance Monitoring on Excel or Google Sheets_Xlookup

[lesfell]

Hello,

I need help in creating an attendance matrix based on the attendance of students from google forms.

Attached is the sample file:
Sheet 1 = response database from google - Column A is timestamp; Column B the ID number
Sheet 2 = attendance matrix - Column A is the ID No; Column B is the student's name (I will do this via lookup) and then columns C to K represent the three days divided into AM, Lunch, and PM

For the three-day event, the class will use the same google form and their attendance will be recorded in sheet 1.

I need a formula or help in building the attendance matrix (Sheet 2) so it reflects whether the student is absent or present at the given days and at a given time range.

Time Ranges are as follows:

AM (7:00 AM to 9:00 AM)
Lunch (12:00 PM to 1:00 PM)
PM (4:30 PM to 5:30 PM)

I appreciate any help or ideas in doing this. Thank you so much!!

 

[vletm]

Could it be something like this?
Usage:
#1Fill Sheet1
#2 Select Sheet2 ... to see data
... You'll see times
#3 Select Sheet1 ... to see is there possible ... absents
... and where data has marked in Sheet2

 

[lesfell]

Could it be something like this?
Usage:
#1Fill Sheet1
#2 Select Sheet2 ... to see data
... You'll see times
#3 Select Sheet1 ... to see is there possible ... absents
... and where data has marked in Sheet2

Hello, yes, it should be something like this. Thank you. But I will be monitoring in real time during AM, Lunch and PM. So I was thinking of just using a combination of IF and XLookup to create sheet 2.

Like if ID No is in sheet 1 and the timestamp is Day 1 and AM, then they should be marked present in day 1 column for AM, otherwise absent. Just something simple like that.

 

[vletm]

I changed file ...
Please check Sheet2?
You should see only ... absents (now yellows) ... if those are more important.
If You could see both, then it will be harder to notice - which belongs to which!

There are some data, which do not match with Your given ... ranges.
Question: how to handle cases if there are missing timestamps?
Your sample data has three timestamps which are out of time ranges (yellow with red arrows; gotta show like this).
... above snapshot shows that there are four more missing timestamps (grays) and whole Day 2 (PM)'s are missing or what?
Green lines shows ... moments of timestamps.
Note: Every time, the whole sheet will create again - except the 1st row.