Create an Excel Time Card Template For Employees

Follow the below steps to create an Excel Time Card Template:

The first thing we need to do is to enter the heading of the time card template in excel; below is the image of the same.
Now, in the “Month” cell (B3), create a dynamic drop-down list in excel of all the months.
For all the employees, there will be standard in time and standard out time. So enter that standard “In Time” and “Out Time” in B4 and B5 cells, respectively.
Under the “Date” column, we need to enter the date, and under the “Time In” we need to record shift “IN” time; under the “Time Out” column, we need to record “OUT” time.
During this “IN & OUT” time, they will go for a standard break, so enter the standard break time as 30 minutes.
Next, we need to calculate how many hours actually they worked in between the 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,
Regular working hours are 8 hours; anything extra will come under “OT Time.” To calculate “OT Time,” enter the below formula.
Now, when it comes to payment, we need to calculate two sets of payments, 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.

I have entered a lengthy formula here; I will break down the formula for your understanding. Let us look at the first formula.

Part 1: =IF(E9>=TIME(8,0,0),800,

This says if the working hour is 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, it says if the working hour is less than 8 hours, but greater than 4 hours then, half of the salary should be paid, and finally, if the working hours are less than 4 hours, then a full day salary will be zero.
We are done with regular payment calculation, now we need to calculate “OT Payment” so enter the below formula to calculate the “Over Time” amount and drag it to other cells also.

Before we calculate, we need to decide on what should be the per hour rate. Generally, per hour rate for OT payment is at the rate of 1.5 of regular hour payment.

This formula will divide the total regular wages per day by 8 hours, so we will get a per hour rating. Then we will multiply the per hour wages into “Over Time” hours; since “Over Time” wages attract a 50% extra rate, we will multiply the OT payment by 1.5.
Now, at last, we need to arrive at the “Total Payment” for this formula would be as follows.
Total Payment = “Regular Payment + OT Payment”.

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

Just in case if you want all the employees’ calculations in a single sheet, then insert One new column, “Employee Name.”