I am trying to create a simple daily time recorded in google sheets. I have created a Google Form named "Time In Records". Each time a user submit a record using that form, it records their name and the time they inputted, as well as the automatic timestamp.
On my other Sheet, which is named "DTR Summary", I am extracting the data from the "Time-In Column" of the "Time In Records" Sheet. I use this code to do that:
=QUERY('TIME IN RECORDS'!$A$2:$E, "select C where E = '"&TEXT($C15,"dd/mm/yyyy")&"' and (B = '"&$A$2&"')")
Basically, I am taking the "Time-In" of a user based on their name(A2) and the date(C15). This works fine, the problem is that I have to manually do this code on the "Time-In Records" Sheet every time a new user submit a data. (The code below is inputted on column E of the "Time-In Records" Sheet) because every time a new data is added, the column E is left blank so I have to manually drag the first cell to the last added data.
=TEXT($A2,"dd/mm/yyyy")
What I would like to happen is to automatically convert the timestamp into a date so that it will match my QUERY. I thought of something like this:
=QUERY('TIME IN RECORDS'!$A$2:$E, "select C where text(A, "dd/mm/yyyy") = '"&TEXT($C15,"dd/mm/yyyy")&"' and (B = '"&$A$2&"')")
Sadly it does not work. I would appreciate any help. Thanks!
For all who are having the same concern, I have figure this out.
On the "Time-In Records" Sheet, I've put this code to column E:
=ArrayFormula(IF(A2:A<>"",TEXT($A2:A,"dd/mm/yyyy"),""))
This automatically converts the timestamp column into date, the current and newly added data.