HomeJAGRUK ENTREPRENEURHow To Make Automated Attendance Sheet In Excel? | Best Free Payroll...

How To Make Automated Attendance Sheet In Excel? | Best Free Payroll App

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.

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.
automated attendance sheet excel

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.
automated attendance sheet excel
  • Now select the cell, beside Month cell, where the drop-down list will appear.
  • From the Data tab, select Data Validation option.
automated attendance sheet excel
  • 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.
automated attendance sheet excel

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.
automated attendance sheet excel
  • 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).
automated attendance sheet excel
  • 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.
automated attendance sheet excel
  • The last date of the month will be =cell of the number of days of the month.
automated attendance sheet excel
  • 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),” “.
automated attendance sheet excel
  • 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.
automated attendance sheet excel
  • 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”).
automated attendance sheet excel
  • Press Enter and drag through all the dates of the month to show corresponding days of the week.
automated attendance sheet excel
  • 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.
automated attendance sheet excel

automated attendance sheet excel

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.
automated attendance sheet excel
  • 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.
automated attendance sheet excel
  • For Description below, select Cell Value Equal To [The Letter You Want to Format].
automated attendance sheet excel
  • 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.
automated attendance sheet excel

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”)).
automated attendance sheet excel
  • Press Enter and drag through the entire section. Thus, all the cells of the attendance section will be automatically filled.
automated attendance sheet excel

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.
automated attendance sheet excel

automated attendance sheet excel
  • 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.
automated attendance sheet excel

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!

Heena Siddique
Bibliophile. Turophile. Foodie. Tea enthusiast. Shopaholic. Sitcom addict. Movie buff.

Related Blogs

Financial Advisor


Follow Us


Jagruk Investor

Jagruk Employees

The term "gig economy" refers to a type of work in which people have temporary jobs. They do separate types of work for income...

Don't Miss

Recent Comments