Time Sheet Record Check for Date Order
I have the employees clock in records here.
What I'm trying to do is, I want to filter the names of these records in "Check" Tab against the date in order.
If the employee is present on the particular day (A Column), I wan to pull the record of that particular employee in the B1 and populate it against the date in A column, If he is absent on that day, It has to be marked as Absent.
I tried doing this in many ways with array formula, Query, and V Look up but ended with errors. can you help me with this?
This is my sample working sheet https://docs.google.com/spreadsheets/d/1bahNEJIweyuvmocYbSR87xL5Nc_IA_HP3qdO7tCKU6w/edit#gid=1124870615
Try this:
=arrayformula(if(A3:A<>"",iferror(vlookup(A3:A&B1,{INT(Records!B:B)&Records!A:A,Records!A:D},{3,4,5},false),),))
Taking into account of future dates and 'ABSENT':
=arrayformula(if(A3:A<>"",iferror(vlookup(A3:A&B1,{INT(Records!B:B)&Records!A:A,Records!A:D},{2,3,4,5},false),IF(A3:A<=TODAY(),"ABSENT","-")),))
Breakdown:
VLOOKUP takes the date from Col A and the name from B1 and then refers to the first {}, to compare against {INT(Records!B:B)&Records!A:A which is the whole day without the time using INT around the date/time, and the person name.
Where there is an exact match, {2,3,4,5} brings back columns 2,3,4,5 from the second array {}.
If there is an error matching IFERROR, then checks IF(A3:A<=TODAY(),"ABSENT","-"). If the day is today or before, it puts "ABSENT", otherwise "-".
if(A3:A<>""... at the start only does the VLOOKUP whilst the value in column A is not blank, otherwise ,) does nothing (the space between , and )).
ARRAYFORMULA works down the sheet.