# How To Calculate Hours Worked in Excel (With Tips)

Updated February 16, 2023

If you're in charge of ensuring employees receive the correct compensation, you may need to track the hours they work. To save time, you can use a program like Microsoft Excel, which allows you to calculate hours and pay using specific functions. This can simplify your calculations and help you organize and find necessary payment information and hours worked.

In this article, we explain why using Excel to track hours is important, show how to calculate hours worked in Excel and offer tips to make calculating hours easier.

## Why it's important to calculate hours worked in Excel

It's important to calculate the hours employees work to ensure they earn compensation for their time and effort. One way you can do this is through the Excel application. Calculating hours in Excel is important because you can use the program to make data or information easier to track and process. Since it has so many functions, can easily calculate data, including hours worked, and provide accurate results. This can make calculating working hours and determining an employee's pay an easier and less time-consuming process for accountants or payroll employees.

Related: How To Include Excel Skills on Your Resume

## How to calculate hours worked in Excel

Follow the steps below to calculate hours worked in Excel:

### 1. Create labeled placeholders

To input the employee's hourly information, create placeholders for each piece of data and information. Once you've built placeholders, you can easily input the necessary information into each cell. The first cell, A1, can be the date you input these hours. You might label this cell "Date." You can then type "Day of the Week" in cell B1 to input the exact day you're calculating hours for.

In cell C1, write "Time In" to represent an employee's initial starting time for their shift. In cell D1, you can input "Time Out," which is the cell they use when clocking out of a shift. The final cell, E1, will be "Hours Worked." This calculates how many hours they completed altogether in a shift. If you're tracking several employees' hours, you can also use A1 to list an employee's name and shift the other columns to the right. So far, your cells should look like this if you're using one Excel spreadsheet to track a single employee's hours:

A B C D E
1 Employee Date Day of the week Time in Time out Hours worked
2
3
4
5

Related: How To Sum Columns in Excel Using Multiple Methods (With Tips)

### 2. Input the times into their respective cells

You can now insert the time employees have worked into their respective cells. Make sure the times you insert are consistent and easily computable by Excel. After you insert the date and day of the week, input the time the employee clocked in by using this format:

hh:mm

Here's an example of correctly inputting work times into Excel:

A B C D E
1 Employee Date Day of the week Time in Time out Hours worked
2 Employee 1 1/6/2020 Monday 9:00am 5:00pm
3
4
5

Related: 8 of the Best Excel Alternatives for Data Analysis

### 3. Use a formula to calculate hours the employee worked

After inputting the information, you can calculate how much the employee worked. You can do this using the function =SUM(D2-C2)*24 in cell E2. Set the cell format displaying "Hours Worked" to "Number" format to produce a correct calculation. You can also format the cell with two decimal places when it creates your final number. Once you input this function, the hours worked can display in its cell. Here is what it may look like:

A B C D E
1 Employee Date Day of the week Time in Time out Hours worked
2 Employee 1 1/6/2020 Monday 9:00am 5:00pm 8
3
4
5

### 4. Determine the employee's pay for that shift

If you're interested in calculating the employee's hourly pay, you can use another simple function to find the calculation. In cell F1, create a new, labeled placeholder titled "Hourly Pay." You can input the pay the employee receives hourly in cell F2. You can then create a new title in cell G1 titled "Total Hourly Pay Amount." This cell calculates the employee's total pay for that specific shift.

You can then click on the G column and set the function to "=SUM(E2*\$F\$2)" to automatically calculate the pay the employee will receive for that specific shift. Your table may now look like this:

A B C D E F G
1 Employee Date Day of the week Time in Time out Hours worked Hourly Pay Total Hourly Pay Amount
2 Employee 1 1/6/2020 Monday 9:00am 5:00pm 8 \$15 \$120
3
4
5

Related: 12 Steps for Getting Into Management (With Benefits)

### 5. Calculate the total weekly hours and pay

You can now calculate the employee's total pay and hours for the week and list it near the bottom of the cell. Write each day of the week in its own row, then create a new cell label titled "TOTAL." The cells next to this one display the total number of hours worked and pay received for the week. You can apply this by using the function "=SUM(E2:E8)" to calculate total hours.

Drag down the total hourly pay amounts from cell F2 to determine how much pay the employee will receive throughout the week. Use "=SUM(F2:F8)" to display the total weekly pay. Here is what the cell may finally look like after applying all these functions:

A B C D E F G
1 Employee Date Day of the week Time in Time out Hours worked Hourly Pay Total Hourly Pay Amount
2 Employee 1 1/6/2020 Monday 9:00am 5:00pm 8 \$15 \$120
3 1/7/2020 Tuesday 10:00 am 4:00 pm 6 \$15 \$90
4 1/8/2020 Wednesday 9:00 am 5:00 pm 8 \$15 \$120
5 1/9/2020 Thursday 9:00 am 5:00 pm 8 \$15 \$120
6 1/10/2020 Friday 9:00 am 5:00 pm 8 \$15 \$120
7 1/11/2020 Saturday
8 1/12/2020 Sunday
Total Hours Worked 38
Total Pay \$570
Showcase your skills with help from a resume expert

## Tips for using Excel for tracking hours

Follow the tips below to properly calculate the hours you work in Excel:

### Verify the formulas and data you inserted are correct

As you input hours worked into Excel, double-check to ensure all the information you input is correct. This provides accurate pay amounts and hours worked. You can also ensure you insert the exact formula correctly into each cell. If the formulas have any typos, you may receive error symbols to notify you that the formula has been inputted incorrectly. If you receive these error symbols, it should look like "#####."

If you see this error message, you can double-check that you're inputting the correct formula into the cell. You may also receive these error symbols if your equation has a negative number.

Related: 6 Essential Accounting Skills

### Keep the formatting neat and clean

Whether you're personally inputting an employee's hours or they're inserting their own, formatting the cells to make it simple for others to read and input their information. Make sure both the formulas and cells have clean data and that you label the cells clearly. This makes it easier for employees to know where to input their data and information. It can also reduce the chances of incorrect information being calculated, which may lead to inaccurate payment for the employee.

Related: Basic Excel Formulas and How To Use Them

### Build a new spreadsheet every pay period

To keep the employee's payment information organized and easy to retrieve, it's often best to build a spreadsheet for each employee according to the various pay periods in which they work. This is why many employers may create a quarterly or biweekly spreadsheet. This keeps the information looking clean and easy to store. If you choose to build spreadsheets for each pay period, you can file them in digital or physical files to retrieve for later reference.

Share:
Indeed Career Services
Indeed Resume
Get noticed by employers
Resume Services
Get your resume reviewed or rewritten
Interview Practice
Practice interviewing with an expert career coach
Book a session
Resume Samples
Kick start your search with templates
Browse resume samples
Salary Calculator
Company Reviews
Access millions of company reviews
Find companies

## Explore more articles

• What Are the 4 Basic Functions of Management?
• How to Write a Proposal Letter (With Template and Example)
• Interview Question: What Is Your Typical Day Like?
• 12 Computer Communication Types You May Use at Work
• Top 23 Professional Fonts (And How To Choose the Right One)
• How To Become a Librarian: Requirements and Steps To Follow
• How To Filter for Unique Values in Excel in 7 Steps