Excel Time Card Template

Time Card Template in Excel

When we work in an organization we need to enter our log in time and log out timings. In modern technology-driven the corporate world we have sophisticated bio-metric machines and swiping machines to record our presence in the office either through fingerprint or through access card. We can download the data from those software’s and do all the calculations in excel itself. In this article, we will show you how to build a time card template in excel. Follow the entire article to learn about it.

How to Create a Time Card Template For Employee in Excel?

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 drop-down list of all the months.

For all the employees there will standard in time and standard out time. So enter that standard “In Time” and “Out Time” in B4 & 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 “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.

Regular working hours is 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 hour 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 full day salary will be zero.

We are done with regular payment calculation, now we need to calculate “OT Payment”, so enter below formula to calculate “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 per hour rating. Then we will multiply the per hour wages into “Over Time” hours, since “Over Time” wages attracts 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.

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’ calculation in a single sheet then insert One 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 need to enter the pivot table to get the summary of all the employees.

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

Things to Remember

• Any exceptional late coming need to be adjusted manually.
• Anything extra payment also should be adjusted outside the time card template in excel sheet.
• Apply the pivot table after all the dates.
• Any holiday should be adjusted manually as well.

Recommended Articles

This has been a guide to Excel Time Card Template. Here we discuss how to create a time card template of employees to record “In” time and “Out” time of particular organizations. You may learn more about excel from the following articles –

• Accounting Excel Templates
• Free Payslip Excel Template
• Timesheet Template in Excel
• Create Blank Invoice Excel Template
• Family Tree Template in Excel
• How to Create Templates in Excel?
• Create a Calendar Template in Excel
• Create an Excel Spreadsheet