Skip to content
Home ยป Automated Attendance Sheet in Excel with Formula | Download Attendance Sheet Template

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

     

    In this video I demonstrate how to create an attendance sheet in Microsoft Excel. The attendance sheet has the following features:

    1) You can select a month and year and it will show a calendar with non-working days formatted in grey. The non-working days include bank-holidays or any other holidays you want to specify

    2) The current day is shown on the calendar in orange

    3) You can specify ‘P’ for present, ‘A’ for absent or ‘H’ for planned absence. These values can only entered on working days

    4) A count of present, absent and planned absence days for the month

    5) A percentage breakdown of present and absent days for the month

    Click here to download the attendance sheet template.

     

    Video Table of Contents

    Use the time stamps below to navigate between the video’s chapters.

    00:00 – Introduction

    01:32 – Create the attendance report drop-downs for month and year

    05:22 – Create the date headings (Excel 365)

    12:51 – Create the date headings (Old Excel)

    17:20 – Conditional formatting to show non-working days in grey, today’s date in orange and P, A, and H in different colours

    26:13 – Data Validation to prevent entry on non-working days

    30:22 – Attendance record summary calculations