Corporate Excel Timesheet Template

In the South African environment, corporate Excel timesheet templates can be a little more complex to create. In a recent consulting assignment we were required to create a timesheet template that would allow a client to reuse it on a number of sites with different employees and different work schedules.

Below some of the features that had to be custom built into the timesheet template.

Allow the template to be used for different sites and different number of employees

This meant that we needed to create master sheets that allowed for different rosters (each site required different numbers of employees for different lengths of time, on different days depending on the site).

As a result the client could use the template on any of their sites even though there were differing shift requirements and staff numbers.

Create the months specific timesheet

The template would, automatically, on a monthly basis, need to be changable to reflect the new months details with regards the:

start and end of month (1) below (this is for July),
any public holidays in that month and
the weekends ((2) below- Sundays only in this case).
This would also need to include the sites roster (3) taking into account the required shifts on each day.

Allow for timesheet capture in human friendly format

In this case the timesheets were captured manually and entered into the template at the end of the month.

In order to avoid finger problem errors, the template capture is in the same style as the timesheet for normal hours and converted via the template into a database.

The features that needed to be included were:

Automatic blacking out of disallowed timeslots
Dropdown selection of only approved employees for that site
Day/ Night differentiator for shifts

‘Special’ hours like sick leave, overtime, COVID related time, suspension etc were captured in a separate input sheet but incorporated into the database for upload.

Convert captured timesheet into a summary report

Based on the timesheets captured together with the special hours, a summary report is instantly created for review.

Convert timesheet into database extract for import into payroll system

The final step (but performed instantly in the template on capture) was to convert the landscape style, human friendly timesheet into a database where each entry is shown as a different data point.

This enabled an easy import into the Payroll system for processing.