One of the most routinary task when processing the payroll is to consolidate and tally all employees’ attendance record. More so when employees clock in using bundy clock before encoding it into an excel timesheet. But because most employees are paid by the number of hours they worked, companies need to track their working hours accurately.

To save time and effort, most companies use a biometric system to capture the clock in and clock out time and export it into excel or into a payroll system. However, if you are not using a payroll system, the overtime and night shift differential are usually not calculated automatically.

Therefore, I have created an excel timesheet calculator that automatically calculates the number of hours of undertime, overtime and night shift differential for companies in the Philippines. Download the excel timesheet template and read the instructions and FAQs below.


Excel Timesheet Template with Formulas in the Philippines

Download the Employee Timesheet here.

Instructions on using the Excel Timesheet Template

This is a basic excel timesheet that automatically calculates the number of hours of normal work, overtime, undertime and night shift differential. It can also take into account of the compressed workweek so companies working more than 8 hours per day can also use this timesheet template in excel.

After downloading the excel timesheet template with formulas, follow this 5 simple steps and read the FAQs below if you have any questions.

  1. Fill in the Employee Name and Department.
  2. Select the Year and Month from the dropdown.
  3. Input the employee’s normal ‘Work Start Time‘. If the ‘Work Start Time‘ is not fixed, leave it blank.
  4. Input all the ‘Time In‘ and ‘Time Out‘.
  5. The rest of this excel timesheet is automatically calculated.

Frequently Asked Questions for the Excel Timesheet Template

  • How to change the ‘Work End Time‘?

The ‘Work End Time‘ is automatically calculated based on the ‘Work Start Time‘ plus ‘No. of Working Hours Without Overtime Pay‘ plus ‘Breaktime‘.

  • What if the employee does not have a fixed ‘Work Start Time‘?

You can leave the ‘Work Start Time‘ blank. If it is blank, then the timesheet calculator will count all the hours worked from ‘Time In‘ to ‘Time Out‘.

  • What is ‘No. of Working Hours Without Overtime Pay‘?

It is the company’s normal working hours. By default, it is set to 8 hours a day. If your company is working on a compressed workweek scheme, you can change it according to your company normal working hours. There will be a warning message, click ‘Yes‘.

  • Why is the ‘Overtime‘ not working?

Make sure the ‘Allow Work After Work End Time‘ under ‘Additional Settings‘ is set to ‘Yes‘. By default, it is already set to ‘Yes‘.

  • Why is the ‘Night Shift Differential‘ not working?

Make sure the time input is correct. The problem usually lies with mixing up of ‘AM‘ and ‘PM‘. The ‘Night Shift Differential‘ also assumes 1 hour of break time with 5 or more hours of work.

  • Why is the ‘Hours Worked‘ always 8 hours?

The maximum number of ‘Hours Worked‘ is the number under ‘No. of Working Hours without Overtime Pay‘. By default, it is set to 8 hours a day. The total number of hours an employee worked a day is the number of ‘Hours Worked‘ plus ‘Overtime

  • What is ‘Allow Work Before Work Start Time‘?

By default, this is set to ‘Yes‘. So for instance, if the company starts work at 8am but the employee ‘Time In‘ at 7am, the employee would be considered working for 1 hour. However, if this is set to ‘No‘, the timesheet calculator will only start counting the ‘Hours Worked‘ from the ‘Work Start Time‘. If ‘Work Start Time‘ is left blank, then timesheet calculator will count all the hours worked from ‘Time In‘ to ‘Time Out‘.

  • What is ‘Allow Work After Work End Time‘?

By default, this is set to ‘Yes‘. So for instance, if the company ends work at 5pm but the employee ‘Time Out‘ at 6pm, the employee would be considered overtime for 1 hour. However, if this is set to ‘No‘, the timesheet calculator will stop counting the ‘Hours Worked‘ upon the ‘Work End Time‘. So the calculator will not count any overtime for the employee. If ‘Work End Time‘ is blank, then the timesheet calculator will count all the hours worked from ‘Time In‘ to ‘Time Out

It is definitely easier to use an attendance monitoring system and a payroll system to process the employee’s payroll. Besides, it is more accurate too.