Creating a Work Attendance Dashboard in Excel

In this video, we'll guide you through the process of creating a work attendance dashboard using Microsoft Excel. For a complete tutorial, join our Channel membership. The template is available at our online store, otherdls.com.

Steps to Create the Dashboard

1. Set Up the Time Sheet:

   • Includes employee name, date, time in, and time out.
   • Follow job timing instructions.

2. Calculate Late Time:

   • Use a formula to determine lateness if the time in is after 8:00 a.m.
   • Subtract the start time from the actual time.
   • Change the cell format to time for accurate results.

3. Calculate Total Working Hours:

   • Subtract time out from time in for total hours worked.
   • Calculate any short time based on shift duration.
   • Note day off, national day, and absentee days.

4. Visual Enhancements:

   • Update fonts, colors, and apply a dark theme.
   • Add a title using a text box.

5. Format Employee Info Worksheet:

   • Enhance feel and look similar to the time sheet.

6. Create Pivot Tables:

   • Choose source data and insert pivot tables.
   • Change number format to time.

7. Extract Values from Pivot Tables:

   • Total attendance days.
   • Times employees attend late (after 8 a.m.).
   • Times employees attend on time (by 8 a.m.).

8. Dashboard Visualization:

   • Display extracted numbers (e.g., total attendance, delays) in the dashboard.
   • Calculate total delay, absent days, and off days.
   • Use formulas to analyze overtime hours.

9. Chart and Employee Details:

   • Insert a line chart for working hours per month.
   • Add selected employee details via a slicer.
   • Display specific employee data using VLOOKUP and slicer selection.

Conclusion

You've successfully created a work attendance data dashboard in Excel. Watch the next video for dashboard design specifics. Purchase this template at otherdls.com. Thank you for watching—like, share, and subscribe to our channel for more content.