The template uses three main sheets (Settings, Time Card Entry and Report Setup) of data entry input and four automated output sheets. Let us split the steps into three phases.
- Entering the Settings
- Entering Time Card data
- Viewing and Printing Timesheet reports
In Settings sheet, we begin by entering Start Date. This is the date from when we want to track time worked by employee.
The template will use the date to auto-populate the dates in the Time Card Entry sheet.
Please do not modify the start date after you start entering time sheet entries in DATA ENTRY sheet.
Weekends, Start of Week and Holidays
The template is designed to fit various scenarios when it comes to Weekends. We can choose which days are weekends. In the above image, we have selected SUN (Sundays) and SAT (Saturdays) as weekends.
Start of Week
We can choose the starting day of a Week. We understand that different companies and different countries may have different weekend days and week beginning days. The template can handle all of them.
We can enter the list of holidays in the holidays table.
- Each date must be entered separately.
- By default, 40 days can be entered as holidays. However, you can unprotect the sheet and add more holidays if needed.
The template has a feature by which it can automatically apply different policy for weekends and holidays.
Overtime policy settings
Now, we are entering the more fun part. Rules and policies around overtime or multiple tiers of pay could vary across companies and countries. We have tried to capture most common set of rules in this template.
There are three components to this.
- Overtime Tiers and Limits
- Policy for Weekends and Holidays
- Policy for Seventh Consecutive work day in week (California State’s Day of Rest law)
Overtime Tiers & Limits
The Excel template can handle up to 3 time-tiers (for example, Regular, Overtime and Double Overtime).
You can modify these inputs to implement several different policies. We will cover many scenarios later in this blog post in detail.
In the screenshot above, we have Regular, Overtime and Double Overtime. The setting chosen above implies the following policy.
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 to up to 12 hours in a day. Employee will earn at DOUBLE OVERTIME rate of 20 for any work beyond 12 hours in a day.
Employee will earn at OVERTIME rate of 15 for hours worked after 40 non-overtime hours in a week.
Policy for Weekends and Holidays
You can apply any of the tiers to Weekends and Holidays.
If we choose ‘Double Overtime’, then all the work done during holidays and weekends will be assigned to ‘Double Overtime’.
To ensure that the policy is correctly implemented, the template also automatically generates this policy text.
Please review and ensure that it accurately reflects your company policy.
Policy for Seventh Consecutive work day in week (California State’s Day of Rest law)
This policy may be applicable only for state of California. However, if you need to implement a specific policy for seventh consecutive work day in a week, then you can use this option.
Just like everything else in the template, it is optional and allows customization.
You can just choose No if this does not apply to your business.
You can also customize the hours limit and the tier that needs to be applied.
The Settings discussed so far are to be entered one-time and we don’t have to change them unless your company’s policies change.
Order of Implementation.
In the above-mentioned 3 components, the following is the order of implementation. If more than one rule was applicable, the higher placed rule is implemented.
- If it is the seventh consecutive workday in a week, that policy is implemented.
- If it is a holiday or a weekend, the time tier chosen for that is implemented. No multiple tiering.
- Otherwise, the multiple tiers policy is implemented.
Time Card Data Entry
Now, we are ready to enter the daily time card entries.
First 4 columns are key Employee specific information such as Unique Employee ID, Employee Name, Role (or Job Title) and Manager name.
Unique Emp ID is critical because that is how we will identify each employee when we generate timesheets. Please ensure that that is unique. You can use any employee IDs in your organization or create a sequential one in this template.
Employee Name, Role and Manager name will appear on the timesheets.
The template also has provision for three more columns that you can use for tracking any additional information for the employees.
Time entry is done using three time columns. Time In, Time Out and Break time.
They are straight forward just like their names indicate.
Enter the starting and ending times of work each day.
We will enter in HH:MM format. Enter just 8:00 for 8:00 AM and 17:30 for 5:30 PM.
Break time is unpaid work and the number of hours entered will be deducted from Work Hours.
We enter break time in HH:MM format but this time it is number of hours and minutes of break. For example, enter 00:30 for 30 minutes of break or 01:30 for 1hr 30 mins of break.
If an employee works across days, the template can automatically handle it. For example, if an employee starts work on 25th June at 8 PM and finishes work on 26th June at 4 AM, then we enter
TIME IN: 20:00 TIME OUT: 4:00
Holidays are shown in blue and Weekends are shown in Yellow.
Viewing and Printing Timesheets
In the Report Set up sheet,
Enter the Employee ID for the employee whose time sheet needs to be generated.
You can also enter a custom field that will be printed in the time sheets.
We can enter the name, a tagline and Address of the company. A Custom Field is provided for Company information as well.
Choose Time Period
The template can generate 4 different timesheets (Weekly, Biweekly, Monthly and Custom date range)
The timesheets are on-demand, in the sense that you enter the pay period and it pulls the corresponding data automatically.
You can enter the time period and click on ‘Click here’ link to go to the timesheet you need. You can also directly go to the tab instead of clicking.
The template has 4 in-built timesheet reports that can be printed on-demand.
- Weekly (7 days)
- BiWeekly (14 days)
- Monthly (Complete Month)
- Custom (Any date range of <=31 days)
All the four timesheets are printer-friendly and can be printed or exported to PDF and saved.
The following shows the layout of the Weekly timesheet template.
Custom fields provided in Report Set up sheet will appear on the Timesheet as well.
Here is a screenshot of the complete page.
BiWeekly timesheet Template
Monthly timesheet Template
It is recommended that you print and save a hard copy or save as PDF and store, depending on your company’s document retention policies.
Export as PDF