From the Blog

Time cards can be an effective device to calculate working hours and payroll with negligible margin for error on a Microsoft Excel file. These cards can be punched into time clocks to measure accurately the number of hours that the employees work, take a break and the number of days they are on leaves and vacation. The efficiency and accuracy would give the management a good grasp over the resources as well.

The daily and weekly calculation can sometimes become cumbersome but not anymore with the time clocks. Excel can further ease the calculation. Employers would need to keep a sheet for each employee to calculate the number of their working hours and leaves. For easy reference, we have illustrated the steps below:

1) Start by renaming the default Sheet 1 with the name of the employee. The first three columns can be used for the name, separated into their first, middle and last name. Next, label cells A1 to C1 as “First”, “Middle” and “Last”, respectively. In the columns from D1 to F1, add “Month”, “Week” and “Date” respectively.

2) Depending on the organization’s working hour, put in the start and end time columns for each on and off clock period. For instance, there are two short breaks and a lunch break. Then, the sheet needs eight columns on row 1 starting from Column H, which can be categorized as “Start Work”, “Break 1 Out”, “Break 1 In”, “Lunch Out”, “Lunch In”, “Break 2 Out”, “Break 2 In” and “End Work” respectively.

3) Select the headers from Column H to O to format the cells. Locate the “Number”, choose “Time” category and a “Type” for the most convenient time format.

4) In cell I1, insert “Day Total” and select the entire column by clicking on the header for column I. Again, format using “Number” and choose the most convenient type.

5) The working hour of the day can be calculated easily. First, enter the formula I2: =(I1-H2, K2-J2,M2-L2, 02-N2) and this adds up the time, while subtracting the breaks.

6) By placing the cursor at the bottom right corner of cell I2, drag it down until row 366 for an annual report.

7) Using select-all button, copy the entire sheet to a clipboard, paste it on a new worksheet. Each worksheet can be renamed for each employer by replacing the default Sheet number.

8) Use View menu to locate the Window group. There are more options such as multiple Freeze Panes to freeze the top row, first column and so on. This process should be repeated for each worksheet.

9) Type the name of the employee in cells A2 to C2, the month in D2, the first date of the week in E2 and so on.

10) Key in the employee’s time card in the respective cells and it is done.

CTS is a key management and time management company, specializing in electronic key management systems, time and attendance solutions, independent and”>synchronized clock systems and others. It also offers”>time cards and time clocks.