WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Time Card Template

Excel Time Card Template

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Create an Excel Time Card Template For Employees

  • Step 1: 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.

Time card Template Example 1

  • Step 2: Now, in the “Month” cell (B3), create a dynamic drop-down list in excel of all the months.

Time card Template Example 1-1

  • Step 3: 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 & B5 cells, respectively.

Time card Template Example 1-2

  • Step 4: 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.

Time card Template Example 1-3.png

  • Step 5: During this “IN & OUT” time, they will go for a standard break, so enter the standard break time as 30 minutes.

Time card Template Example 1-4

  • Step 6: 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,

Time card Template Example 1-5

  • Step 7: Regular working hours are 8 hours; anything extra will come under “OT Time.” To calculate “OT Time,” enter the below formula.

Time card Template Example 1-6

  • Step 8: 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.

Time card Template Example 1-7

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.

  • Step 9: 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.

Time card Template Example 1-8

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

  • Step 10: Now, at last, we need to arrive at the “Total Payment” for this formula would be as follows.
Total Payment = “Regular Payment + OT Payment”.

TC Template Example 1-9

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

TC Template Example 1-10

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

TC Template Example 1-12

Once we end up with all the days in the payroll month, we need to enter the pivot table to get a summary of all the employees.

TC Template Example 1-11

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

You can download this Time Card Excel Template here – Time Card Excel Template

Things to Remember

  • Any exceptional late coming needs to be adjusted manually.
  • Anything extra payment also should be adjusted outside the time card template in the 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 for employees to record “In” time and “Out” time of particular organizations. You can learn more about excel from the following articles –

  • Excel Roster Template
  • Time Log Template
  • Free Payslip Excel Template
  • Timesheet Template in Excel
11 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Time Card Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More