In a recent MS Excel consulting assignment we were required to assist in converting a timesheet roster to a payroll upload with a focus on extracting the leave dates.
The catch was that the upload needed to convert leave days indicated on the timesheet into leave ranges i.e. 3 days leave shown on the timesheet on the 14th, 15th, 16th November needed to be uploaded as leave starting on the 14th and ending on the 16th.
Converting timesheet entries
As shown below, the timesheet was created in landscape format with each date shown in the columns and the activity for that employee in the rows (NT= Normal Time, LE= Leave, AW= AWOL).
Payroll Upload Requirements
This needed to be automated so that once the timesheet roster was received, they could click a button and get the reports below.
Summary normalised database
A normalised database where the matrix style of the timesheet is replaced with a database which shows an entry for each day and each employee on a seperate line.
Leave Start/ End dates per employee per leave batch
An upload report which converts the timesheets shown above into a database (shown below) where each run of leave days per employee appears on a seperate line with:
- Employee Number
- Start date of that batch of leave
- End date of that batch of leave
- Leave type
So if it was 1 days leave, the start and end dates are the same, but more days meant we needed to find the correct start and end dates and effectively ignore the days inbetween.
Summary of the months timesheet
For comparison purposes to the payroll system, a summary of the timesheets for that month. As shown below we used a Pivot Table to provide a quick summary by employee and timesheet entry type.
Timesheet Roster To Payroll Software
If you have a requirement for converting a timesheet roster to a payroll upload using Excel, send an email to info@AuditExcel.co.za.
We also offer an online training course relevant to HR and Payroll professionals which may interest you.