Skip to content

Automated Attendance Sheet in Excel with Formula | Download Attendance Sheet Template

    This video tutorial shows you how to create an attendance sheet in Excel. Watch the video above and download the featured file using the link below.

    Download the featured attendance worksheet here

    Excel Automated Attendance Sheet – Overview

    • Month and Year Selection: Drop-down menus for choosing the month and year.
    • Dynamic Date Display: Automatically adjusts to the correct number of days for the selected month, including leap years.
    • Weekend and Holiday Highlighting: Gray background to indicate non-working days, including weekends and bank holidays.
    • Current Day Indicator: An orange background highlights the current day.
    • Attendance Marking: Allows users to mark attendance with “P” for Present, “A” for Absent, and “H” for Holiday.
    • Conditional Formatting: Colours cells based on attendance status (orange for P, blue for A, and green for H).
    • Data Validation: Restricts input on non-working days and only allows “P,” “A,” or “H” inputs on working days.
    • Attendance Summary: Automatically calculates the total present, absent and holiday days and their respective percentages for each employee, factoring in working days, and highlighting planned leave.

    Functions Used

    • SEQUENCE: (For Excel 365 or newer) Generates a sequence of numbers, enabling the automatic generation of day numbers.
    • EOMONTH: Returns the last day of a month, which is crucial for calculating the number of days in a month.
    • DATE: Creates a date value from year, month, and day components.
    • MONTH: Extracts the month number from a date or text representation of a month.
    • DAYS: Calculates the number of days between two dates.
    • WEEKDAY: Returns the day of the week as a number. This is used to identify weekends for conditional formatting, and also within data validation.
    • COUNTIF: Counts cells that meet a specific criteria which is used for summary calculations.
    • NETWORKDAYS Calculates the number of working days between two dates, excluding weekends and optionally, holidays.
    • TODAY – Returns the current date for highlighting.
    • IF statements The attendance sheet relies heavily on using IF statements to conditionally return values.
    • Conditional Formatting: The attendance sheet extensively uses conditional formatting rules to add visual cues to the sheet.
    • Data Validation: The tutorial emphasises the importance of using data validation to ensure data accuracy and prevent errors. It attendance sheet includes custom rules to prevent input on weekends and holidays, and only allows specific attendance codes.
    • Named Ranges and Tables: The attendance sheet uses named ranges and tables to make the formulas dynamic and more manageable.

    FAQs

    1. How can I create a drop-down list for selecting the month and year in my attendance sheet? To create a month drop-down, start by listing the months (January, February, etc.) in a column on a separate ‘lists’ sheet. Then, in your attendance sheet, use the ‘Data Validation’ feature. Select ‘List’ as the ‘Allow’ type, and specify the range of months from your lists sheet as the ‘Source’. For the year drop-down, list the years (e.g. 2024, 2025, etc) in a column. Create a named reference to this column of years by selecting the column and using ‘Name Manager’. Use the named reference as the source for the ‘Data Validation’ ‘List’.
    2. How does the attendance sheet automatically display the correct number of days for the selected month? The sheet uses formulas to calculate the number of days in the selected month dynamically. The core function is the SEQUENCE function (for newer Excel versions) which generates a series of numbers representing each day of the month. This function uses the EOMONTH, DATE, and MONTH functions to determine the number of days in a month. For older versions of Excel, the date of the first day of the month is calculated, and then each subsequent day is incremented until the end of the month using a series of ‘if’ statements.
    3. How do I automatically display the day of the week (e.g., Mon, Tue) alongside the date numbers? After generating the sequence of date numbers, another row converts those numbers into dates by using a DATE function. This function takes the year and month selected in the drop-downs, and the day number. The dates are then formatted to show the day of the week (e.g. ‘ddd’) using custom formatting.
    4. How are weekends and bank holidays identified and displayed with a grey background? A conditional formatting rule is applied to the date rows using the WEEKDAY and COUNTIF functions. The WEEKDAY function identifies Saturdays and Sundays (days 6 and 7 respectively). The COUNTIF function checks if a date exists in a named reference to a column of holiday dates that are in a table on the ‘lists’ sheet. The conditional format is applied to display the cell with a grey, patterned background if it is a weekend, bank holiday or blank.
    5. How does the sheet highlight the current day with an orange background? Another conditional formatting rule uses the TODAY function to compare the date in each cell of the grid to the current date. When a match is found, the cell background and border is formatted with an orange colour.
    6. How can I use the letters P, A, and H to record presence, absence, and holiday and how do they get their coloured backgrounds? Conditional formatting rules are set up to change the cell fill colour and font style for the letters P, A, and H. If a cell contains a ‘P’, it will get an orange background, an ‘A’ will get a blue background, and an ‘H’ will get a green background.
    7. How does the sheet prevent data entry errors in non-working days or invalid letters? Data validation is used to restrict entries. A custom formula uses the same logic as the conditional formatting for non-working days (using WEEKDAY and COUNTIF) combined with an OR statement for validating data entry. The formula ensures that data can only be entered if the date is not a Saturday, Sunday or a bank holiday and that only the letters p, a or h are used as inputs. The data validation includes a custom error message that is displayed if invalid data is entered.
    8. How does the summary table calculate the total present, absent, and holiday days, and the percentage attendance? The summary table uses the COUNTIF function to count the occurrences of ‘P’, ‘A’, and ‘H’ in the attendance grid for each employee. The percentage attendance is then calculated by dividing the total present days by the number of working days using the NETWORKDAYS function (excluding holidays), with additional adjustments when the current day is not at the end of the month. The percentage absence is calculated in a similar manner but the number of holidays are not taken away from the working days calculation in this case.