I made the attachment in 2009 for your reference.
To tell the truth, it is more painful to do this kind of attendance with Excel, because the data has to be judged many times.
I use macros made by Acess to automatically import monthly data (similar to your original data), process them in Acess, and then export the results to Excel.
If you know about databases such as Access or SQL, you can try the following methods:
1.？ Because the date of each month is different, the columns of the result table must be different. Use cross-table query, the date is a column, and import the results into the result table. Every time you run an Access macro, the table is deleted and recreated from a cross-table query.
2.？ Judge the validity of employee number. Some tourists also punch in. Get rid of them! @#￥#! Some card numbers are wrong, for example, there are multiple spaces in front, so Trim is used to correct them.
2.？ Employees may punch in many times, making progress first and taking out food last. (Access with function)
3.？ Multi-class judgment. The more troublesome thing is the alternate-day class, and the others are simpler. I do a single analysis in multiple shifts, and then use Union to unify the results into a query.
4.？ The rest is to connect with HR DB to export some department and name information and design some printed reports.
5.？ Copy the contents of the result table to Windows? Clipboard. Then export the data from the clipboard through Excel macro and process some formats.