It is also important to learn how to troubleshoot, fix problems and trace errors while working with the excel. Today in this section we are going to discuss the formulas we can use within the attendance sheet maintained in excel. Managing the attendance of employees is one of the most important parts for the department of human resource, this procedure is required for the calculation of leave, balances, salary process and much more.
In excel formulas are the most important and the best method of doing different things. If you are a regular user of excel then it is really necessary for you to work on using these formulas for working with dates and time, manipulating text, index, and match, lookup values with VLOOKUP, criteria of count and sum, creating dynamic ranges, etc.
Attendance Sheet For Employees Excel 2019
If you are running any small organization and do not have many employees then in this condition it will be best to maintain the attendance in an excel template, sometimes attendance management is also accounted as the tracker sheet as it can keep the entire record for present and absent days. If you use this attendance sheet in excel with formula then it will be proved as a great help for you in managing the leave and attendance of your employees with full visualization.
Attendance Sheet For Employees Excel-Download In PDF
You can also use this sheet to monitor the leave and attendance for a month of your employees. After every month you can easily create a new attendance sheet in which you can keep the record. What you all have to do is every time you just need to update the year and month in your sheet and keep the rest of thing same as before.
Tracker Templates Excel 2019
Tracker Templates Excel-Download In PDF
If you are in need of adding more employees then you can simply copy and paste the formula in the template. As within the policies of left by companies each company provides different kinds of leave like casual leave, sick leave, earned leave, etc so you can also change the name of left as per the requirement and the policies of your company. You can also keep the record of balanced leaves in this sheet for each employee when you will update the sheet it will automatically calculate the closing balance of leaves.
Contents of Employee Attendance Sheet Template
Printable Employee Attendance Calendar Excel 2019
Printable Employee Attendance Calendar Excel- Download PDF
Today in this article you are going to receive the employee attendance sheet template this attendance tracker excel is having four parts into it, header section, employee details section, attendance record section, summary section. Let us come to know about them in detail.
- Header Section
This is the first and most important part of the attendance sheet which includes the name of the company, heading of the sheet-like attendance sheet, information of the respective month for which the attendance is going to be marked. So, all these details are enclosed within the header section.
- Employee Details Section
This is the second part of an attendance sheet which includes the details of employees. In it, you can easily mention all professional details like the name of an employee, designation, employee id, a department in which he works. All these details are provided in this second section.
- Attendance Record Section
This is the third section of an attendance sheet, it is the most crucial section which contains month heading along with the days and dates of the entire month. When you provide the date in date section you just only have to enter the first date, rest of the dates will be filled automatically as per the formula. You can apply the formula =F3+1 or any other, and for the part of days, they are needed to be filled manually. To create a list of present and absent of employees you will get a drop down list along with the validation. In the drop-down list you can most probably found the following symbols:
P = Present
A = Absent
OT = Over Time
L = Late
SL = Sick Leave
EL = Earned Leave
PL = Paid Leave
CL = Casual Leave
ML = Maternity Leave
NH = National Holiday
FH = Festive Holiday
H = Other Holidays
TR = Training
After mentioning the present or absent you can simply find out the total of employees who are present each day by using the following formula
=COUNTIF(F4: F53, “P”)
- Summary Section
Printable Tracker Templates Excel 2019
Printable Tracker Templates Excel- Download In PDF
It is the last section of your attendance sheet which will contain the detail of individual total for each employee under the following headings:
Present
Absent
Late
Over time
Sick leave
Maternity leave
Paid leave
Earned leave
Casual leave
Festive holiday
National holiday
Another holiday
Training
So, this was an entire layout of employee vacation tracker excels in the attendance sheet. By using this method it becomes very easy to keep the record of each employee in the database without using many efforts. It will also help you in getting all your results without much efforts by simply entering the formulas.
How To Create a Basic Attendance Sheet In Excel
In school life, we have seen our teachers who keep the record of all students in their attendance sheet. As students were less so it was easy to keep the record of each and every student manually in a register. If you are working in any organization or company you know that it is very important to mark the attendance as on this basis the employees get their payment or salary. Due to a large number of people, it is almost difficult to keep a manual record therefore to overcome this situation we are having a perfect solution of keeping the record in attendance sheet in excel.
Now, the question is here how? Most of the people do not know how to create an attendance sheet in excel if you are also one of them then worry not there are many more people like you. Although you can also use the templates of attendance in excel which are being provided in this article but still if you wanted to learn about being more accurate and precise then it will be best to create an attendance sheet by your own. This will improve your knowledge and will help you in gaining more systematic variations. So, the steps for creating an excel sheet are as follows:
- Fill in the Basic Labels
Before starting working let us clear you it is just a basic attendance sheet and is nowhere related to the professional one, here we are going to provide an attendance sheet for students. So first of all open, the excel in your computer and in the first column type Attendance and in the same row go with the Names. For this let us go with ‘student 1’ in the slot of a name. After typing student 1 select the box and click on ctrl+enter and then drag the pointer downwards from the bottom right corner of the box, it will automatically fill up the names for other students too.
- Add and Format Dates
Now, we are going to include dates, formatting them and removing the weekends. So at first enter date like 28/3/2019, after typing simply right click and select the option of “Format cells”. A dialogue box will appear from which you can simply select the option of Number and then from the list choose for last option of custom. By this, we will add a day of the week in this date format so this will become more precise and it will be shown up like “Thurs 28/3/19”. So for getting this format to enter ddd m/d/y as shown in the below image. You are completely free to go with any other format as here mmm stands for the month, ddd will stand for the day.
So, after it to fill the days and dates simply select the first one and drag it in the direction as shown in the below image, the rest of the portion will get filled automatically. If you wanted to remove the option of Sunday then excel is having a perfect tool for this too.
Simply click at the tiny box that appears at the corner of the selected area and then from the opened list you can choose the option of “Fill weekdays”, it will remove all dates which come on Sunday.
- Add a Column For Total
As it is important to keep the total of attendance so in this step we are going to add a separate column for a total. You can keep the column of total anywhere as per your desire and comfortability. Now, for it just simply select the entire area in the sheet you should also leave some empty slots for the new students. Next, go to the option of a grid and from there select “All borders”. You can also add color to it for highlighting or making it unique from other rows and columns. Next, we are going to add a formula in your attendance sheet which will help you in total. So in the first slot enter =SUM (click at the box of the first date and then scroll till the end of the row and enter). After it drags the same box from bottom right to the last entry of slots.
- Remove Zeros For Non-Existent Students
If you wanted to remove the zero value for the slots that are blank or not assigned to any student then in such situation you have to add an IF statement to the function of SUM. For this you have to click at the first box of total and then enter IF ( click on the box of student) and then continue with the following formula IF(A3=”” this will indicate that for the box which is empty something will happen, it will do not show zero instead you will be going to see total value. So this formula will be completed by the addition of SUM function it will end like the following :
=IF(A3=””,””,SUM(C3:N3))
- Create Printable area for Final Tally
When you feel the need of printing this simple tally for each and every student you are in need of selecting both of the columns after it simply goes to the option of Page Layout and then select the option of “Print Area” and then choose the “Set Print Area”.
So, by following the above steps your attendance sheet is complete, you can see that it is not very difficult to create an attendance sheet in excel you just have to follow few simple steps and everything is set,
Employee Attendance Tracker
Employee Attendance Tracker-Download PDF
In the market, there are various online employee attendance trackers which can keep a record of all employees in them. This software is specifically designed as per your requirements. We are here also providing you the online employee attendance tracker by which you will be able to keep a track of the attendance sheet. This will help you in the following ways:
- Synchronize with your attendance device
- Register attendance
- Regularize data
- Retrieve tailor-made reports
Apart from this, you can also use the attendance tracking templates, there are various templates available online which lower down your work and help you in reaching your goal as earliest you can. These tracking templates are being used for keeping the record of attendance for individual either they are student or employee it is necessary to maintain a safe and secured file these templates do the same job for you. It is very easy to handle and simple to use these templates because they help you in analyzing the attendance and lowering down your efforts. We are here also providing the same for you.
Summary,
Whether we are studying or working in all conditions attendance is counted as a very important part of our schedule. If you are an employee then each and every attendance matters a lot as a single error can create changes in your salary. So it becomes very important for the HR to manage the attendance in a secured and safe way where no one can edit it.
There is no best place of making attendance in an excel sheet it will help you in keeping a secure and safe record with you. We hope that you are also looking forward to different attendance sheet formulas for excel as creating a file in excel require formulas and here we are providing each and every important formula to you.
Similarly, we are also providing attendance sheet templates in excel which can be downloaded easily and can be used without needing any edit. This will reduce down your work and everything will become easy so it is also the best option to use templates. Apart from this, you can also keep the record track of all the attendances by simply opting for the online or offline employee attendance tracker.