Why buy an expensive attendance management tool for your startup if you can track the attendance of the team in Excel? Yes! You can create an Attendance tracker in Excel easily. In this article, we will learn how to do so.
Step1: Create 12 sheets for Every Month in a workbook
If you plan to track attendance for a year, you will need to create each month's sheet in Excel.
Step 2: Add Columns for each date in each month's sheet.
Now create a table that contains the names of your teammates, a column for totals and 30 (or number of days a month) columns with date and weekday as column headings.
To get the name of weekday you can look up the calendar or you can use the formula to copy it in the rest of the cells.
| =TEXT(date,"ddd") |
You can read about it here.
Format the weekends and holidays dark and fill them with fixed values like Weekend/Holiday as shown in the image below.
Do the same for each sheet.
Step 3. Fix the possible inputs using data validation for each open cell.
Now everyone can insert their attendance in the sheet but they may enter random text. Some may write P for present, or Present, or per, etc. Data uniformity is mandatory in any attendance management system.
To allow users to only write P or A for present and absent respectively, we can use data validation.
Select any cell, go to data in ribbon and click on data validation. Select list from options and write A,P in the text box.
Hit OK.
Copy this validation for the whole open range of data (open range means cell where user can insert values).
Step 3: Lock all cells except where attendance needs to be entered.
Select date a date column. For example, select 1-Jan. Right now click on the selected range and go to the cell formatting. Go to protection. Uncheck the locked checkbox. Hit OK. Now copy this range to all open date ranges.
This will allow entry into these cells only when we protect the worksheets using worksheets protection menu. Thus your formulas, fomattings will be intact and users can only modify their attendance.
So how do you calculate the present days? Well everyone has their own formulas for calculating attendance. I will discuss mine here. You can make changes as per your attendance sheet requirement.
I count the total number of present days as total days in a month, minus the number of days absent. This will keep holidays and weekends in check. They will automatically be counted as working days.
So the excel formula for counting present days will be like:
| =COUNT(dates)-COUNTIF(attendance_range, "A") |
This will by default keep everyone present for the whole month until you have marked them absent on the sheet.
In the example, the formula is:
| =COUNT($C$2:$AG$2)-COUNTIF(C3:AG3,"A") |
I have written this formula in cell B3 and then copied it down. You can see that 27 day's are shown as a present. Even though I have not filled all the presence cells. You can keep it this way if you want them by default to be present. Or if you want them by default to be absent, check all the cells as absent. This will keep only present days count in the present calculation.
Step 5: Protect the Sheet
Now that we have done everything on this sheet. Let's protect it so that no one can alter the formula or the formatting on the sheet.
Go to the review tab in the ribbon. Find the Protect Sheet menu. Click on it. It will open a dialog box that will ask for the permissions you want to give to the users. Check all the permissions you would like to allow. I only want the user to be able to fill attendance with nothing else. So I am gonna keep it as it is.
You should use a password that you can remember easily. Otherwise, anyone can unlock it and alter the attendance workbook.
Now if you try to alter non attendance cells, excel will not allow you to do so. However, you can alter the attendance cells as we have unprotected them.
Step 6: Do the above procedure for all the month sheets
Do the same thing for each month sheet. The best way is to copy the same sheet and make 12 sheets out of it. Unprotect them and make the necessary changes and then protect them again.
Although we have all the sheets ready to be used for attendance filling, we don't have one place to monitor them all.
The administration would like to see all the attendance at one place instead of on different sheets. We need to create a master attendance sheet.
Step 7: Prepare Master Table To Monitor Attendance at one place in Excel
For that, prepare a table that contains the name of team mates as row headings and name of month as column headings. See the image below.
Step 7: Lookup Attendance of Team From Each Month Sheet
To look up attendance from the sheet we can have a simple VLOOKUP formula but then we will have to do it 12 times for each sheet. But you know that we can have one formula to look up from multiple sheets.
Use this formula in Cell C3 and copy in the rest of the sheets.
| =VLOOKUP($A3,INDIRECT(C$2&"!$A$3:$B$12"),2,0) |
Since we know that all the sheets have total attendance in range B3:B12, we use the INDIRECT function to fetch values from multiple sheets. When you copy this formula to the right, it looks up values in Feb sheets.
Caution: make sure that the sheet names and the column headings in the master are the same otherwise this formula will not work.
Step 8: Use the Sum function to get all the present days of the year of a team mate.
This is optional. If you like you can calculate the total present days of your employees throughout the year by simply using the sum formula.
And that is it. We have our Excel Attendance Management System ready. You can modify this as per your requirement. Use it for salary calculation, incentive calculation or anything else. This tool will not fail you.
You can make changes to calculate holidays and weekends separately in each sheet. Then subtract them from the total present days, to calculate total working days. You can also include L for Leave in the drop down to mark leave of employees.
So yeah guys, this is how you can create an excel attendance management system for your startup. It is cheap and highly flexible. I hope this tutorial helps you in creating your own excel attendance workbook. If you have any questions let me know in the comments section below.
Related Articles:
Lookup From Variable Tables Using INDIRECT: To lookup from a table variable in Excel, we can use the INDIRECT function. The INDIRECT function will take the text range and convert it into the actual attendance range.
Use INDEX and MATCH to Lookup Value: The INDEX-MATCH formula is used to look up dynamically and precisely a value in a given table. This is an alternative to the VLOOKUP function and it overcomes the shortcomings of the VLOOKUP function.
Use VLOOKUP from Two or More Lookup Tables | To lookup from multiple tables we can take an IFERROR approach. To look up from multiple tables takes the error as a switch for the next table. Another method can be an If approach.
How to do Case Sensitive Lookup in Excel | the excel's VLOOKUP function isn’t case sensitive and it will return the first matched value from the list. INDEX-MATCH is no exception but it can be modified to make its case sensitive. Let’s see how…
Lookup Frequently Appearing Text with Criteria in Excel | The lookup most frequently appears in text in a range we use the INDEX-MATCH with MODE function. Here's the method.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
