How to Calculate Time on Excel Spreadsheet

Written by Jack Lloyd

Last Updated: January 12, 2020 Tested

This wikiHow teaches you how to create a time sheet for payroll in Microsoft Excel. You can do this on both Windows and Mac computers by using a pre-made template or by creating your own time sheet.

Method 1

Method 1 of 2:

Using Templates

1
Open Microsoft Excel. It's a dark-green app with a white "X" on it.
2
Click the search bar. It's at the top of the Excel window.
- On Mac, first click File in the upper-left corner, then click New from Template… in the drop-down menu.
Advertisement
3
Type time sheet into the search bar and press ↵ Enter. This will search Microsoft's database of templates for time sheet templates.
4
Select a template. Click on a template that you want to use. Its page will open, allowing you to view the template's format and appearance.
- If you decide that you don't like the template that you've selected, click X in the template's window to close it.
5
Click Create. It's to the right of the template's preview. Doing so will create the template in Excel.
6
Wait for your template to load. This will likely take a few seconds. Once the template loads, you can proceed with creating the time sheet.
7
Enter any necessary information. Each template will vary slightly from other templates, but you'll typically have the option of entering the following:
- Rate per hour - The amount that you're paying the employee in question per hour of work.
- Employee identification - Your employee's name, ID number, and so on.
8
Enter the time worked in the appropriate column. Most time sheets will have the days of the week listed in a column on the far-left side of the page, meaning that you'll input the hours worked in the "Time" (or similar) column to the right of the "Days" column.
- For example: if an employee worked eight hours on a Monday during the first week of a month, you would find the "Monday" cell in the "Week 1" column and type in 8.0.
9
Review the results. Every time sheet template will calculate the total number of hours that you enter and, if you've entered a rate per hour value, display the total that the employee in question earned.
10
Save your time sheet. To do so:
- Windows - Click File, click Save As, double-click This PC, click a save location on the left side of the window, type the document's name (e.g., "January Time Sheet") into the "File name" text box, and click Save.
- Mac - Click File, click Save As..., enter the document's name (e.g., "January Time Sheet") in the "Save As" field, select a save location by clicking the "Where" box and clicking a folder, and click Save.

Method 2

Method 2 of 2:

Creating a Manual Time Sheet

1
Open Microsoft Excel. Its app icon resembles a white "X" on a dark-green background.
2
Click Blank workbook. This white icon is in the upper-left side of the Excel "New" page.
- Skip this step on a Mac.
3
Enter your headers. Type the following headers into the following cells:
- A1 - Type in Day
- B1 - Type in Week 1
- C1 - Type in Week 2
- You'll add Week [number] in the D1, E1, and F1 (if necessary) cells as well.
- If you observe overtime, you can add an Overtime heading in cell C1 for Week 1, cell E1 for Week 2, and so on.
4
Enter your days of the week. In cells A2 through A8, type in Sunday through Saturday in order.
5
Add a rate. Type Rate into cell A9, then enter the rate per hour in cell B9. For example, if the rate is $15.25 per hour, you'll type 15.25 into cell B9.
6
Add a "Total" row. Type Total into cell A10. This is where the total number of hours worked will go.
- If you want to use overtime as well, type Overtime into A11 and enter the overtime rate into B11.
7
Enter the formula for Week 1. This formula will add up the hours worked from Sunday through Saturday and then multiply the sum by the rate. To do this:
- Click the Week 1 "Total" cell, which should be B10.
- Type in =sum(B2:B8)*B9 and press ↵ Enter.
8
Enter the formula for the remaining weeks. To do so, simply copy the formula that you entered for Week 1, then paste it into the "Total" row below your selected week and replace the B2:B8 section with your week's column letter (e.g., C2:C8).
- If you're using overtime, you can use this formula to calculate overtime by replacing the B9 value with B11. For example, if your Week 1 "Overtime" column is in column C, you'd enter =sum(C2:C8)*B11 into cell C10.
- If you're using overtime, you can create a "Final Total" section by typing Final Total into cell A12, typing =sum(B10,C10) into cell B12, and repeating for each "Week [number]" column with the correct column letters.
9
Fill out the time sheet. Enter the hours worked for each day in the "Week 1" column. You should see the hours and the respective amount earned total up at the bottom of your sheet in the "Total" section.
- If you have overtime enabled, fill out this column as well. The "Final Total" section will change to reflect the regular wages and the overtime combined.
10
Save your time sheet. To do so:
- Windows - Click File, click Save As, double-click This PC, click a save location on the left side of the window, type the document's name (e.g., "January Time Sheet") into the "File name" text box, and click Save.
- Mac - Click File, click Save As..., enter the document's name (e.g., "January Time Sheet") in the "Save As" field, select a save location by clicking the "Where" box and clicking a folder, and click Save.

Community Q&A

Question

How do I enter floating holidays or vacation days, if I work part time (4.5 hours)?

Community Answer

Enter 4 1/2.
Question

When I want to add the total time, for example 18:19 + 22:45=41:04, why do I keep getting 17:04 as my answer?

Community Answer

Because you did not convert it to decimal format, so it thinks you are adding time together to get the next day. Remember, time in excel is a fraction, so 6:19 pm day 0 + 22:45 = 5:04 PM Day 1.