Create an Excel Time Card Template For Employees

Now, increase the date every day, we need to enter the “Time In” and “Time Out” timings of each employee, so automatically, it will calculate “Working Time,” “OT Time,” “Regular Payment,” “OT Payment” and “Total Payment” columns as we have inserted the formulas.

  • First, we need to insert the heading of the time card template in Excel. Below is the image. In the “Month” cell (B3), we must create a dynamic drop-down list in excel of all the months in Excel. There will be standard in time and standard out time for all the employees. So, we must enter the standard “In Time” and “Out Time” in B4 and B5 cells, respectively. Under the “Date” column, we need to enter the date. Then, under the “Time In,” we need to record the shift “IN” time. Finally, under the “Time Out” column, we need to register “OUT” time. They will go for a standard break during this “IN” and “OUT” time, so we must enter the standard “Break Time,” like 30 minutes. Next, we need to calculate how many hours they worked between standard in time and standard out time. So, below is the formula to calculate the actual working time. Working Time = (Time Out – Time In) – Break Time, The regular working hours are 8 hours. Therefore, anything extra will come under “OT Time.” To calculate “OT Time,” we must enter the below formula. Now, we need to calculate two sets of payments when it comes to payment. One is regular hour’s wages, and the second one is OT hours. To calculate regular hourly wages, we need to enter the standard wages for 8 hours. We have entered a lengthy formula here. We will break down the formula for understanding. Let us look at the first formula.Part 1: =IF(E9>=TIME(8,0,0),800,Here, if the working hours are greater than 8 hours, then 800 is the number of daily wages.Part 2: IF(AND(E9<TIME(8,0,0),E9>=TIME(4,0,0)),800/2,0))This part is the critical part. If the working hour is less than 8 hours, but greater than 4 hours, they should pay half of the salary. Finally, if the working hours are less than 4 hours, a full day’s salary will be zero. Now, we have completed the “Regular Payment” calculations. Next, we need to calculate “OT Payment,” so we must insert the formula below to calculate the “overtime” amount and drag it to other cells. Before we calculate, we must decide on the per-hour rate. Generally, the per hour rate for “OT Payment” is 1.5 of the regular hour payment.This formula will divide the daily weekly wage by 8 hours to get a per-hour rating. Then, we will multiply the per hour wages into “OT Time” hours. Finally, since “overtime” wages attract a 50% extra rate, we will multiply the “OT Payment” by 1.5. Now, we need to arrive at the “Total Payment.”The formula would be as follows: Total Payment = Regular Payment + OT Payment.

Working Time = (Time Out – Time In) – Break Time,

We have entered a lengthy formula here. We will break down the formula for understanding. Let us look at the first formula.Part 1: =IF(E9>=TIME(8,0,0),800,Here, if the working hours are greater than 8 hours, then 800 is the number of daily wages.Part 2: IF(AND(E9<TIME(8,0,0),E9>=TIME(4,0,0)),800/2,0))This part is the critical part. If the working hour is less than 8 hours, but greater than 4 hours, they should pay half of the salary. Finally, if the working hours are less than 4 hours, a full day’s salary will be zero.

Before we calculate, we must decide on the per-hour rate. Generally, the per hour rate for “OT Payment” is 1.5 of the regular hour payment.This formula will divide the daily weekly wage by 8 hours to get a per-hour rating. Then, we will multiply the per hour wages into “OT Time” hours. Finally, since “overtime” wages attract a 50% extra rate, we will multiply the “OT Payment” by 1.5.

Total Payment = Regular Payment + OT Payment.

If we want all the employees’ calculations in a single sheet, insert a new column, “Employee Name.”

Finally, our time card template is ready to record time for employees.

Once we end up with all the days in the payrollThe Days In The PayrollPayroll refers to the overall compensation payable by any organization to its employees on a certain date for a specific period of services they have provided in the entity. This total net pay comprises salary, wages, bonus, commission, deduction, perquisites, and other benefits.read more month, we must enter the PivotTable to get a summary of all the employees.

Like this, we can create a “TIME CARD TEMPLATE” in Excel.

Things to Remember

  • Any exceptional late coming needs to be adjusted manually.Anything extra payment also should be accommodated outside the Timecard template in the Excel sheet.We must Apply the pivot tableApply The Pivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more after all the dates.We should adjust any holiday manually.

This article is a guide to Excel Time Card Template. Here we discuss creating a time card template for employees to record “In” time and “Out” time of particular organizations. You can learn more about Excel from the following articles: –

  • Excel Roster TemplateTime Log TemplateFree Payslip Excel TemplateTimesheet Template in Excel