How to Create an Excel Punch-in Time Card

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Punching the clock can be simple with an Excel time card.
Image Credit: Thinkstock/Comstock/Getty Images

Tracking employee time can be a headache in many small offices. Microsoft Excel can help. This application is a powerful number cruncher, and you can use it to track your employees' daily and weekly hours worked using a simple time card.

Advertisement

Set Up Your Time Sheet

Video of the Day

Step 1

Select Columns A-H and set a uniform column width of "14."

Video of the Day

Step 2

Choose "Orientation" from the Page Layout menu and select "Landscape."

Advertisement

Step 3

Enter your time sheet title in Row 1.

Step 4

Enter "Employee" in cell A2.

Step 5

Enter "Week Beginning" in cell D2.

Advertisement

Step 6

Enter "Total Hours" in cell G2.

Step 7

Enter the seven days of the week in Row 3 beginning in Column B. The first day entered should be the start of your workweek.

Step 8

Enter "Date" in cell A4, "Time In" in cell A5, "Time Out" in cell A6, "Lunch Break" in cell A7, "Time In" in cell A8, "Time Out" in cell A9 and "Total" in cell A10.

Advertisement

Format Your Time Sheet

Step 1

Select cells B4 through H4 as well as E2 and format as "Date," choosing your preferred date format.

Advertisement

Step 2

Select cells B5 through H9 and format as "Time" displaying hour: minute AM/PM.

Advertisement

Step 3

Highlight cells B5 through H9 again and select "Data Validation" from the Data menu.

Step 4

Set Data Validation to allow "Time" with "Ignore Blank" checked for Data "Between" "00:00 AM" and "11:59 PM." In the Error Alert tab, ensure that "Show Error Alert After Invalid Date Is Entered" is checked. Set Style to "Stop." Enter the Error Alert Title as "Invalid Time Format." Input the Error Message as "Enter time as hour: minutes AM/PM. For example: 8:15 AM."

Advertisement

Step 5

Select cells B10 through H10 and format as "Time" using a 24-hour format.

Enter Formulas in Your Time Sheet

Step 1

Enter the following formula in cell B10, then copy and paste the formula into cells C10 through H10: "=SUM(B9-B8)+(B6-B5)".

Advertisement

Step 2

Enter the Formula "=E2" into cell B4.

Step 3

Enter the formula "=B4+1" into cell C4. Copy and paste the formula in cells D4 through H4.

Step 4

Enter the formula, "=(SUM(B10:H10)*24)" in cell H2.

Advertisement

Advertisement