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.
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."
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.
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.
Format Your Time Sheet
Step 1
Select cells B4 through H4 as well as E2 and format as "Date," choosing your preferred date format.
Step 2
Select cells B5 through H9 and format as "Time" displaying hour: minute AM/PM.
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."
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)".
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.