All labour law compliances have their foundation in the bookkeeping of an attendance register. Since, the number of paydays of an employee directly affects his salary and other labour law scheme payments. Hence, in this article, we will discuss the step-by-step process of creating an automated attendance sheet in excel. Having this automated attendance sheet will make your compliances upkeeping much easier.
Table of Contents
USP of having automated attendance sheet in excel
- All days will automatically appear for all months upon changing months in an automated attendance sheet excel.
- Present days and rest days will appear automatically against the employee’s row of attendance in an automated attendance sheet excel.
- Total number of present, leave and paydays calculation will appear automatically in an automated attendance sheet excel.
Important values to input in an attendance register
- Present days – all the days when the employee is present in a given month is marked as P.
- Rest/Weekly off – all employees should get atleast one day of rest in week. Some employers give two days of rest as well. This is also called weekly off and is marked as R.
- Absent – any day when an employee did not report to work is considered as absent from work and marked as A.
- Paid leave – if employee takes a paid leave then it is marked as L.
- Sick leave – if employee takes a sick leave then it is marked as S.
- Festival leave -if the company is celebrating any festival and not having regular working hours then it is marked as F. This is considered as a paid leave too.
Steps for creating automated attendance sheet in excel
Creating attendance sheet layout
- The automated attendance sheet excel will have a heading in the first row as – Muster Roll Register.
- Second and third rows will have the company’s name, company’s address, month name, days in the month, first day of month and last day of month.
- The next row, first column will have serial number.
- This will be followed by Name of Employee, Father’s Name of Employee and Employee Code.
- The right most rows of the automated attendance sheet excel will have the calculation of Present Days, Leave Days, Rest Days, Festival Days and Total Paydays for all employees.
Creating automated month list
- To create a drop-down list for Month cell, start by writing the first date of every month for a given financial year somewhere separately at the bottom of the excel sheet.
- Now select the cell, beside Month cell, where the drop-down list will appear.
- From the Data tab, select Data Validation option.
- For Allow tab, select List from the drop-down options given.
- For Source tab, select the previously written rows of the first dates of every month for the financial year and press Enter.
- Now select OK and your cell will have a drop-down list of all the months in the financial year.
Setting specific date and day layout
- To set a particular format of date select the cell, right-click on it and select option Format Cells.
- Go to Number tab.
- From the Category, select Date option and pick a format from the options displayed on the right hand column.
- Additionally, you can also input Location to a specific country to get date formats of that country.
- Moreover, you can select Custom option and create your own date format.
- Lastly, click on OK to finalize the date format.
Creating automated number of days in the month
- Select the particular cell for the number of days in the month and type =EOM(start date of month cell,0).
- This will display the number of days in the month selected on the next month.
- To change the format of the display, right-click the cell and go to Format Cells.
Setting first date and last date of each month
- The first date of the month will be =cell of the month name.
- The last date of the month will be =cell of the number of days of the month.
- To display both these values as dd-mm-yy, just go to Format Cells and pick the given format.
Setting individual days of the month
- To fill up the excel sheet automatically with all the days of the month, we need to note that the number of days displayed should never exceed the number of days in the month selected.
- Now, fill in the first day of the month as 1.
- For the next cell type the formula =(cell of first date of month)<$(cell of number of days in month),(cell next to first date of month),” “.
- Now, drag the second date of month to the end of columns for days of the month. This will automatically fill up all the days there are in a month.
- If we change the month from the above cell, the number of dates will change accordingly.
- To represent which date corresponds to which day of the week, select cell of first date of month.
- In the below column, which will show the day of the week type =TEXT(cell of first date,”dddd”).
- Press Enter and drag through all the dates of the month to show corresponding days of the week.
- To allot specific numbers to each day of the week, select the first cell and type =IF((cell of first date of month)<=(cell of no of days in month),WEEKDAY(cell of first date),0). Drag the cells along the rows to automatically fill value for each day of the week.
Setting values for employees rest days and pay days
- Under the Rest Day column for each employee, fill in the number corresponding to their off day.
- Select all the cells which will have the employees attendance data.
- Next, go to Data Validation from Data tab and select List under Allow category. Under Source, fill in the alphabets corresponding to different values of attendance. Example, P for Present Day, A for Absent Day, L for Paid Leave, S for Sick Leave, R for Rest Day and F for Festival Leave. Click on OK.
- Now you can view a drop-down list for each employee’s attendance record for each day. Thus, you can select the option which is valid per employee.
- To set individual colour codes to individual days, select the entire section.
- Click on Home then click on Conditional Formatting and select Manage Rules.
- Now click on New Rule. Furthermore, under Select a Rule Type, pick Format Only Cells That Contain.
- For Description below, select Cell Value Equal To [The Letter You Want to Format].
- Move on to Format tab below, and go to Font tab. Pick the font colour and type you want for the letter. Click OK.
- Hence, every letter will show up as a different font type or colour.
Setting automated letters for rest days
- To have a specific letter automatically fill up all the rest days and rest be filled up with all the present days we do the following.
- Select one cell from the section and type the formula =IF(first cell value=0,” “,IF(next cell=first cell value,”R”,”P”)).
- Press Enter and drag through the entire section. Thus, all the cells of the attendance section will be automatically filled.
Counting attendance of each employee
- Select the first cell of the total days of counting attendance.
- Type the formula, =COUNTIF((full range of all days for one employee),”P”). Therefore, this will count all the Present Days marked as P for one employee in a month.
- Drag it downwards to automatically fill present days count for all employees.
- Similarly, for counting the other days of leave in the month, use the COUNTIF formula and replace the letter with the letter assigned for that given day.
- To find the total pay day for each employee, type the formula =SUM(range of all individual count of days). Press Enter and drag through the whole section.
- This information can be used for your payroll processing excel sheet. View details for it in How To Make Payroll In Excel? | Payroll Calculation.
Watch the complete tutorial in the video below:
Best Free Employee Attendance App
As seen from the explanation above, maintaining a company’s payroll is a laborious task. Moreover, the first week of every month is hectic as HR/consultants/business owners need to do all attendance calculation and labour law compliances to ensure that all employees get their salary before the 7th. Even after maintaining an excel attendance sheet diligently, the file may end up getting corrupted. Or there may be some calculation errors.
Quick Payroll app
Keeping these difficulties in mind, the Labour Law team devised a free employee attendance app. This app is called the Quick Payroll App.
Currently, one has to manually maintain a sheet of employee attendance. Once the month is over, all the days of present, absent, leave, etc., have to be manually counted and fed into the attendance software. The more employees in a company, the bigger the hassle of this task. Alternately, if one keeps an automated attendance sheet excel then the file may get corrupt, there may be calculation errors or the file version may be wrong.
Download the Quick Payroll App and leave your ratings too!
App features
Process Payrolls & Mark Attendance of the staff which can be easily managed from your mobile device, Quick Payroll app is a 100% Indian app created by the trusted Labor Law Advisor team.
Payroll Processing
NEW Feature Now one can easily process payroll for free with Quick Payroll App. Get accurate payrolls for all the staff and choose from 3 different payroll calculation methods.
Employee Attendance Management
Manage and record attendance of the employees on a daily, weekly, or monthly basis. Auto calculation on the basis of attendance marked and mark Present, Absent, Half-day, Sick Leave and other options.
Smart Auto Attendance
Smart Auto Attendance feature is an exclusive feature that marks auto attendance of all active employees on the basis of information provided by the business. No need to go and mark present daily for all employees.
Track Working Time
NEW Feature Now one can Track Working time of the Employee with Working Time Feature. Mark Punch-in Punch-out time and calculate overall hours worked.
Employee Advances
NEW Feature Pay Advance to the Employee and keep a track of all the Advance paid in Quick Payroll App.
Download Reports
Download Daily Report, Monthly Report, Muster Roll, Advance Report, Custom Report etc on a Monthly basis or individual employee basis. Thus, get all the necessary information in Excel format, which can also be shared with the payroll consultant.
Add Multiple Businesses
Add multiple businesses to record and manage employee attendance. Furthermore, the easy to switch business option gives the app a seamless user experience.
Bilingual (English & Hindi)
NEW Feature Quick Payroll App is now available in Hindi as well. Use the whole app in Hindi which makes day-to-day work easier.
Auto Backup
All the data is linked to employer’s mobile number so no need to worry about the data. One can easily log in to the account on any other device and see all the past records.
100% Free App
Quick Payroll is a 100% Free app which is made in India by the Trusted Labour Law Advisor team. All the services offered by this app are absolutely free.
Best for HR and Payroll Consultants
If you are an HR or Payroll Consultant, this app is best for you. Manage and record daily attendance and download the reports to process the payroll.
Learn how to use the Quick Payroll app.
Watch the video below for more details.
Join the LLA telegram group for frequent updates and documents.
Download the telegram group and search ‘Labour Law Advisor’ or follow the link – t.me/JoinLLA
It’s FREE!
