Weekly Attendance Tracker in Google Sheets

Summary

In this tutorial, PK demonstrates how to create a dynamic weekly attendance sheet in Google Sheets. The sheet automatically calculates attendance and displays percentages for each employee based on their status (present, absent, leave, or be off). With features like date selection, dynamic supervisor lists, and progress circle charts, this tool makes attendance tracking efficient. PK also covers how to customize the sheet to adjust for working days and provides step-by-step instructions to build the system from scratch. The tutorial offers a comprehensive guide for managing employee attendance with ease and precision.

Takeaways

• 😀 You can create a dynamic weekly attendance sheet in Google Sheets, allowing easy tracking of employee attendance.
• 😀 The sheet features a date selection from a calendar, which automatically populates the dates for the week starting from the selected date.
• 😀 You can input the employee ID, name, and select the supervisor name from a dropdown menu in the sheet.
• 😀 The attendance can be marked as 'Present', 'Absent', 'Leave', or 'Be Off' and the sheet calculates the count and percentage of each category automatically.
• 😀 The attendance percentages are displayed visually with progress circles showing the present, absent, and leave percentages for each employee.
• 😀 If the week starting date is changed, the dates automatically update, but you must manually reset attendance entries for the new week.
• 😀 The sheet allows customization, such as adjusting the number of working days (e.g., 5 or 6), which recalculates attendance percentages accordingly.
• 😀 Dropdown lists are used for selecting supervisors, and these lists can be dynamically populated from a separate range in the sheet.
• 😀 Formulas such as 'COUNTIF' and 'TEXT' functions are used to calculate present, absent, and leave counts, and to format the date and day of the week.
• 😀 Sparkline charts are used to visually display attendance data in a bar format, with color coding to indicate present, absent, and leave statuses.
• 😀 The sheet also features a support sheet that aggregates the total attendance data, showing overall present, absent, and leave percentages for all employees.

Q & A

What is the purpose of the weekly attendance sheet in the video?

The weekly attendance sheet is designed to help track employee attendance, including marking whether they are present, absent, on leave, or off, with automatic calculations for each status and percentages displayed for the entire week.

How does the date selection work in the attendance sheet?

The user can select a start date for the week using a calendar, which will automatically populate the seven days of the week in the sheet, allowing easy tracking of attendance for those dates.

What happens when the date is changed in the sheet?

When the date is changed, the dates of the week update automatically. However, attendance data does not change, so users must manually delete the previous data before marking attendance again for the new week.

How are the attendance statuses (Present, Absent, Leave, Off) marked?

Attendance is marked by selecting an option from a drop-down list in the sheet. Each status is assigned a different color (light green for present, light red for absent, sky blue for leave, and gray for off).

What formula is used to calculate the count of 'Present', 'Absent', and 'Leave' days?

The count is calculated using the 'COUNTIF' formula, which counts the occurrences of each status (Present, Absent, or Leave) in the respective columns.

How are the present, absent, and leave percentages calculated?

The percentages are calculated by dividing the count of each status by the total number of working days in the week (as defined in the settings), which can be adjusted based on the number of working days (e.g., 5 or 6 days).

What is the purpose of the Sparkline formula in the attendance sheet?

The Sparkline formula creates a visual representation of the percentages for Present, Absent, and Leave, using small bar charts within the cells to quickly indicate the attendance status of each employee.

How are the overall attendance statistics displayed?

Overall statistics, such as total present, absent, and leave counts for all employees, are shown in a separate support sheet. The percentages for each status are also calculated in this sheet and used to create progress circle charts for visual representation.

How are the progress circle charts created?

Progress circle charts are created using donut charts, where the present percentage is shown in green, and the blank portion is calculated as 1 minus the present percentage. The absent and leave percentages are also displayed using similar charts with different colors.

How is the supervisor's name added to the attendance sheet?

The supervisor's name is added via a drop-down list, which is created by selecting a range of supervisor names from a separate list in the settings sheet. This list is dynamically linked to the drop-down in the attendance sheet.