As a non-profit manager, you probably need to track your team’s work hours, calculate overtime, and monitor each employee’s vacation days.
In this article, we’ll show you how to manage this time tracking using Excel. We’ll also provide a free template to help you get started.
Enter Basic Rules for the Employee
These details will serve as references for formulas that automatically calculate overtime, time-off, sick leave, and vacation.
In an Excel file, create a table with the following information:
- Hours worked per day
- Vacation days per year
- Sick days per year
Rename the sheet "Rules."
Create the First Week
To make your timesheets in Excel clear and easy to fill out, we recommend creating one sheet per week.
Create a new spreadsheet and name it "Week 1."
Here, we’ll start by creating the first week completely so it can then be copied for the following weeks.
Fields to Fill In
Create a new spreadsheet in the same Excel file and make a table with one column for each day, Monday to Sunday, and one row for each of the following:
- Hours worked
- Vacation hours
- Sick hours
Formulas
Then, add three rows with values that will be filled automatically by formulas:
- Total
- Overtime
- Time-off
In these rows, enter the following formulas in the Monday column:
- Total:
=SUM(B2:B4) - Overtime:
=B5-Rules!$B$1 - Time-off:
=Rules!$B$1-B5
Now, copy these formulas into the columns up to Sunday, adjusting column letters as needed (Tuesday = C, Wednesday = D, etc.).
Tip: To copy cells and automatically adjust references, select the edge of the first cell and drag it to the right.
Next, update the formulas in cells for non-working days to replace references to the rules with 0 (typically Saturday and Sunday but could also include Friday or other days).
Now add three more columns:
- Total
- Accrued
- In Bank
In the "Total" column, enter the formula =SUM(B2:H2) in row 2, then copy it down to row 7, adjusting row numbers in the formulas as needed.
In the "Accrued" column, the formulas will be =Rules!B2/52*Rules!B1-I3 for cell J3 and =Rules!B3/52*Rules!B1-I4 for cell J4.
In the "In Bank" column, the formulas will be =J3 for cell H3, =J4 for cell H4, and =I6 for cell K6.
The first weekly sheet is now ready. You can fill it out to test its functionality before moving to the next step.
Add Formatting (Optional)
If you want to make timesheets more user-friendly, you can add formatting to this first week. For example, use conditional formatting to highlight certain information.
Graying out cells containing formulas can also help your team know which cells to fill in.
Create Additional Weeks
Now that the first week is ready, you can reuse this setup to create the following 52 weeks.
If you filled out the first week to test it, you can now delete the test data to avoid copying it into other weeks.
Duplicate the First Spreadsheet
Right-click the tab for the first week’s spreadsheet, and Excel will give you the option to copy it.
Once this week is copied, rename it "Week 2."
Next, update these three formulas:
- K3 becomes
=J3+'Week 1'!K3 - K4 becomes
=J4+'Week 1'!K4 - K6 becomes
=I6+'Week 1'!K6
Duplicate Additional Sheets
Repeat this process until you have the necessary 53 weeks. Adjust the "In Bank" formulas each week to reference the previous week.
Adapt for Each Employee
Now that this template is created, you can save it under different names for each team member.
If someone works different hours or has more vacation than the rest of the team, simply change the corresponding number on the "Rules" sheet in their file. There’s no need to modify it for each week; it will be done automatically.
Get Our Free Excel Template
If you prefer to use our template, enter your details below, and we’ll send you a free Excel timesheet template. You’ll be able to improve your team’s time tracking right away.
Feuille de temps
We developed Feuille de Temps to simplify time tracking for managers.
In addition to automatically calculating vacation, sick leave, and overtime hours, Feuille de Temps offers many features not available in Excel:
- View of the hour banks for the entire team in one place
- Ability to send reminders to employees with overdue timesheets
- Overtime premiums to comply with labor regulations
- Reporting for accounting or audits
- Time tracking by project