Advanced Excel Monthly Timesheet 2024 template

26 dec

Categories: Techno  | Tags: leave timesheet Employee attendance template Monthly statistics week Working Sick Excel Timesheet Holiday xlsx

Half a year ago, I needed to implement a simple timesheet for employees in a company. It was about self-service counting of hours of attendance at work. Nothing could be easier than opening Excel and using a template. Or download a template from the Internet. After all, an attendance list is a basic tool in every business. Well, I found them, but only English-oriented ones, spanning weeks, calculating weekly wages. I wanted monthly. The best I've found were biweekly ones. So, I made my own Excel, which is not that simple anymore, but it has a lot of cool things.

  • In the attachment, you will find an Excel sheet with the employee's timesheet. Here are some of the features of this sheet.
  • The spreadsheet works all year round (the current version is adjusted to 2024)
  • On monthly cards, the employee records entry and exit times, possible overtime, sickness and holidays.
  • The sheet calculates employee statistics based on the settings from the dashboard tab. How many hours he worked, how many hours remained to be worked in the month, remaining leave and sick leave.
  • Holiday and sick leave data carries over between months.
  • It is possible to freely configure the working week (less than the standard 5 working days), the number of hours per week, including official holidays (marked on the monthly form)

What doesn't the timesheet do?

  • I didn't want to use scripts that require lowering security settings. Therefore, some minor automations are missing.
  • It is not possible to set a different number of hours for different days of the week (and verify whether the employee worked long enough each day)
  • It is not possible to set different configurations of working days for different weeks (this sheet is intended for people working on fixed hours and fixed days of the week)
  • The Sick and Vacation fields display icons and only count full days (after entering 1 in the field). If you have a better idea for displaying time off and sick leave, please write, I will be happy to change this mechanic, although I have no idea how.
  • This is a simple tool for templated solution. If you need something more complicated, don't use Excel, but some SaaS tool like Crewmeister, which allows you to handle attendance lists in a civilized way.

What can be set in a timesheet?

Changes made on the Dashboard page will be reflected on the individual month pages.

  1. Company name
  2. Address
  3. In the "User" field: Employee's name and surname
  4. In the "Number of working hours weekly" field, the number of hours to be worked per week. In most cases, full-time work is 40 hours +-5
  5. In the "Number of working days" field - the number of working days in a week. The next field "Number of working hours daily" is automatically calculated from this and the previous field, which is necessary for the correct calculation of hours worked.
  6. In the "Working pattern" field, enter seven 0s and 1s. Each 1 is a day off, and a 0 is a working day in the week (starting on Monday). 0000011 – means working Monday-Friday, 1111100 means working weekend, and for example 0010011 means off on Wednesday, Saturday and Sunday.
  7. Fill the "Number of vacation days" with the annual limit of vacation days
  8. "Number of sick days" means the number of sick days paid by the employer (I wrote the sheet for Irish requirements)
  9. "Work start month" useful for employees who start work in the middle of the year.
  10. The "Bank holidays" column contains a list of bank holidays. The list is in the "Bank holidays" range and will include 10 bank holidays. If more is needed, the scope needs to be increased.
    Current bank holidays are Irish

Timesheets Dashboard

On the "Month" page

Complete fields "In" (1), "Out"(2), "Extra"(3) in the hourly notation 2:00 3:45 12:00. Entering a number representing only an hour (e.g. 12) will result in incorrect counting of hours worked.

When you enter "1" in the "Sick"(4) and "Vacation"(5) fields, they display an icon and subtract one day from the pool of days off or sick leave.

Field “Working” is calculated.

The file is not foolproof, so users should have some computer literacy (eg not filling computed fields).

Enjoy, and comment.

Timesheet month page

Advanced Excel Monthly Timesheet 2024 template download

Return to index