TRANSFERRED FROM EXCEL WITHOUT OVERTIME
I didn’t expect that after 3-4 years, there are still students who like to use such timesheets.
This is an attendance template made by Lu Zigang HR in 2016.
This is the template that the trainees are doing now, isn’t it quite similar?
This is a record exported from the attendance machine, there may be multiple clock-ins in and out of work, how to transform it into the above template?
Qin involves a lot of knowledge, and Lu Zi slowly talks about it one by one.
1.
Convert the datetime to 9/1nday format
9/1 is the month/day, n is a fixed character, and the day represents the day of the week.
Here with the help of the TEXT function conversion, m is the month, d is the day, !!n is the meaning of forced displayn, and aaaa is the day of the week.
=TEXT(D2,”m/d!!naaa”)
2.
Extract the time from the datetime
The TEXT function is very powerful, can convert many formats, and the extraction time is also possible. h is the hour and m is the minute.
=TEXT(D2,”hh:mm”)
3.
Judge commuting based on name and date
Going to work is the first time every person clocks in every day.
The end of the day is the last time everyone clocks in every day. Of course, this judgment of leaving work does not need to be too precise, and it is enough to classify everything that is not going to work as off work. Because later with the help of the LOOKUP function, it is to find the last value that meets the conditions, no matter how many times you punch in the middle, you will find the last one.
This way it is easier to judge.
With the help of the COUNTIFS function, determine whether it is the first time to clock in, if so, return to work, otherwise return to work.
=IF(COUNTIFS(B$2:B2,B2,G$2:G2,G2)=1, “Go to work”, “Get off work”)
Once the preparation is done, all that’s left is to reference the data into the template.
4.
Quote each person’s daily clock-in time
You need to use 3 conditions to find at the same time, name, date, and commute.
=IFERROR(LOOKUP(1,0/((LOOKUP(“seat”,$A$3:$A 3)=ALL!$B$2:$B$115)*(ALL!$G$2:$G$115=G$2)*(CREW!$I$2:$I$115=$F 3)),ALL!$H$2:$H$115),””)
LOOKUP (“seat”, $A$3:$A 3) means that the merged cells are filled with names.
There are many syntaxes for the LOOKUP function, and the rest is the classic lookup pattern.
=LOOKUP(1,0/((lookup value1=lookup area1)*(lookup value2=lookup area2)*(lookup value n=lookup area n)), return range)
Finally, the nested IFERROR function is to make the display blank without the corresponding value, which is more beautiful.
Timesheets are really a very difficult problem in Excel, involving a lot of knowledge, each company uses a basic different timesheet, increasing the difficulty of learning.
Accompany you to learn Excel, is it enough for a lifetime?
