Convert Timesheet Roster To Payroll Upload

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).

timesheet roster to payroll

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.

Timesheet Roster To Payroll

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.

Timesheet Roster To Payroll

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

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

We also offer an online training course relevant to HR and Payroll professionals which may interest you.