Excel Time Card Template

Follow the below steps to create an Excel time card template:

1. First, we need to insert the heading of the time card template in Excel. Below is the image.

   
   

2. In the “Month” cell (B3), we must create a dynamic drop-down list in excel of all the months in Excel.

   
   

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

   
   

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

   
   

5. They will go for a standard break during this IN and OUT time, so we must enter the standard Break Time, like 30 minutes.

   
   

6. 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,
   
   

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

   
   

8. 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(E9TIME(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.
    

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

10. Now, we need to arrive at the Total Payment. The formula would be as follows:

    Total Payment = Regular Payment + OT Payment.
    
    

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.

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

• 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 table after all the dates.
• We should adjust any holiday manually.