Google Sheets are very helpful when it comes to organizing the data in one place. With the help of various Google Sheet functions, we can create amazing templates such as Scorecard, Annual Budget, To-Do Lists, Attendance, Leave Tracker, and so on.
In this article, let’s understand how to create a Leave Tracker Template with the help of Google Sheet tips provided on this page. Also if you like the leave tracker template which has been created by us, you can make a copy of it and use it for further reference.
|
Table of Contents |
Google Sheets Leave Tracker Template
Here is the snapshot of my sample leave tracker.
- How to Make an Organization Chart in Google Sheets? – Create an Org Chart in Google Sheets
- How to Extract the Year from Google Sheets-YEAR Function in Google Sheets
- Funnel Charts In Google Sheets – How To Make Funnel Templates In Google Spreadsheet?
How to Create Leave Tracker Sheets in Google Sheets?
Follow the steps as listed below to create a Leave Tracker Sheet in Google Sheets:
- Step 1: Open the Google Spreadsheet.
- Step 2: Create a Sheet tab and name it as Attendance Key.
- Step 3: In the Attendance Key tab, Enter the number of leave types you are offering to your employees or students. In the above example, I have created 4 types of attendance keys namely Paid Leave, Loss of Pay, CompOff, Maternity Leave.
Refer to the image below.
- Step 4: Now create another tab and name it as Leave Tracker Sheet. In this example, I am creating September Leave Tracker Sheet.
- Step 5: In cells A and B, we have listed out the employee ID. You can also modify this cell with the employee name or other unique ID of the employee.
- Step 6: Now in cell C2, enter the date from which you tracking the leaves in the format of MM/DD/YYYY. In our case, I am tracking the leave of employees from 4th September 2021.
- Step 7: Now move to cell C3 and enter the formula as “=C2“. This will generate the day of the dates entered in cell C2.
- Step 8: Select Cell C2 & C3 and drag the fill handle. This will auto populate the date and dates till the column you drag the fill handle
- Step 9: Once the sufficient date and days are filled out, Add four more columns.
- Step 10: Now, enter the following formulas in each column respectively. So that it will fetch the data entered in Attendance Key Tab
- =‘Attendance key’!$C7 (C7 is Paid Leave)
- =‘Attendance key’!$C8 (C8 is Loss of Pay)
- =‘Attendance key’!$C9 (C9 is CompOff)
- =‘Attendance key’!$C10 (C10 is Maternity Leave)
- In my case, I have created 4 additional columns from the cell W. Thus the four columns I have created are W, X, Y, Z.
- Step 11: Now move to the Paid leave cell and enter the formula as “=countif($C4:$V4,W$3)“
- Step 12: Next to the Loss of Pay cell, enter the formula as “=countif($C4:$V4,X$3)“. In CompOff and Maternity Leave columns, enter the formulas “=countif($C4:$V4,Y$3)” and “=countif($C4:$V4,Z$3)” respectively.
- Step 13: Now move to the cell C1 and enter the formula as “=iferror(concatenate(” Enter “, ‘Attendance key’!$B$7, ” for “,‘Attendance key’!$C$7, “, “, ‘Attendance key’!$B$8, ” for “, ‘Attendance key’!$C$8, “, “, ‘Attendance key’!$B$9, ” for “, ‘Attendance key’!$C$9, “, and “, ‘Attendance key’!$B$10, ” for “, ‘Attendance key’!$C$10, “. Use the ‘Attendance key’ tab to customize. “))“. You will the results as shown below:
- Step 14: The leave tracker is created. Now all you have to do is enter the type of leave the employee has taken. Based on the type of leave taken, the sheet will update the number of leave and the type of leave that employee has taken.
In this example, I have mentioned that employee 1 has taken 2 maternity leaves. Thus the maternity leave tab shows the total number of maternity leaves taken by the employee.
Download Link of Google Sheets Leave Tracker Template
Click on the following link to make a copy of the Leave Tracker Sheet.
Leave Tracker Sheet Download Link
In order to create a month-wise leave tracker sheet, add M=months by duplicating the ‘Leave Tracker-September’ sheet tab. New tabs will reference this same attendance key.