In Excel, how to calculate whether employees are late, leave early or absent from work according to the opening time? So do you know how excel makes the attendance sheet of late, early leave and absenteeism? The following is some information about the attendance sheet made by Excel for late, early leave and absenteeism for your reference.
In the above table, column a records the time of coming to work, that is, punch in when you enter the company. This time is the time of going to work. Column B is the time when the employee leaves the company, that is, the off-duty time. Now, what we need to do is to calculate whether the employees are late, leave early or absent from work according to these two times.
I. calculation of absenteeism
It's easy to calculate this. As long as the employee doesn't have the opening time in the morning, it means he doesn't come to work. Naturally, he is absent from work.
The formula used in E2 is: = if (A2 = "", "absenteeism", ""), and then fill it down to get whether it is absenteeism.
II. Calculate whether it is late
The standard for calculating whether to be late is: whether the opening time exceeds the working time specified by the company. Here, it is assumed to be 8:00 a.m. if it exceeds, it is late, otherwise it is early or just in time.
The formula used in C2 cell is: = if (A2 = "", "", if ((a2 - "8:00") * 24 * 60 > 0, "late", "")) and then fill down to get the result.
First of all, if you are late, you should pay attention to whether you are absent from work. If you are not absent from work, you should first judge whether you are absent from work. Only when there is no absenteeism and card swiping time can this situation be included in the judgment of whether it is late. The judgment standard is based on 8:00 as the reference object. If it is later than 8:00, it is late, otherwise it is normal to work (for example, it is normal to come in advance and on time).
III. calculate whether to leave early
The calculation method is similar to the calculation method of late, but there is no more introduction. The formula is: = if (A2 = "", "", if ((B2 - "11:30") * 24 * 60 < 0, "leave early", ""))
Knowledge development:
You may have seen the formula just now. Let's look at it again and give an explanation.
Late formula: = if (A2 = "", "", if ((a2 - "8:00") * 24 * 60 > 0, "late", ""))
Early leave formula: = if (A2 = "", "", if ((B2 - "11:30") * 24 * 60 < 0, "early leave", ""))
In the formula: (a2 - "8:00") * 24 * 60 > 0 and (B2 - "11:30") * 24 * 60 < 0, what does this mean? This refers to subtracting two times, then multiplying by 24 hours and then multiplying by 60 minutes, which will get a number. Take this number as the reference object to judge whether you are late or leave early.
For example: = ("8:01" - "8:00") * 24 * 60 think about it. What should be the result? The result is to subtract 8:00 from 8:01 and subtract the two times.
If it is ("8:01" - "8:00"), the result is: 0.0006944497. It's just a decimal. It's difficult to judge whether we are late. What we need is how many minutes the two times are subtracted. As long as we get the minutes, we can know whether we are late. Therefore, the public formula is: = ("8:01" - "8:00") * 24 * 60
See, multiply by 24 and multiply by 60 to get the minutes. If the number of minutes obtained is equal to 0, it means that it is opened after 8 o'clock, and it must be late. If the number is equal to or less than 0, it means that it is on time or in advance, and it is not late. Well, everything else is similar, so I won't explain it any more.