- 1). Open an Excel spreadsheet. Right-click on the tab along the bottom of the first worksheet, and rename it using your employee's name. Create a new sheet, and label it for each subsequent employee.
- 2). Create a "Date" column in cell A1 of your first worksheet. Format it with the date format you prefer by highlighting the column, right-clicking and selecting "Format Cells." Click on the "Date" option, and select your preferred date format.
- 3). Create your time columns. This will include "Time In," "Lunch Out," "Lunch In" and "Time Out" in cells B1, C1, D1 and E1, respectively. Format these cells to military time.
- 4). Create the columns "Total Hours (Non-Overtime)," "Overtime" and "Total Hours" in cells F1, G1 and H1, respectively. There is no need to format these columns, as their default setting (Numbers) is the correct setting. If your employee does not work overtime, you need only the "Total Hours" column.
- 5). Freeze the top row. This will allow you to continue to view the column titles as you scroll down the spreadsheet.
- 6). Create the appropriate formulas. Enter the formula "=SUM(H2-G2)" for the column "Total Hours (Non-Overtime)." Enter the formula "=SUM(H2-8)" for the column "Overtime." Enter the formula "=SUM((C2-B2)+(E2-D2))*24" for "Total Hours."
- 7). Transfer the formulas and column headings to your subsequent worksheets.
SHARE
