How to Track Hours Worked in Excel + Calculate Pay (with video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

If you need to track your hours worked so that you can bill your clients, this article is for you. Read on for a step by step guide on how to track hours worked in Excel and calculate pay.

We’ll also go over a simple way you can convert your total hours worked from time format to a regular number that you can more easily use in calculations.

How to Track Hours Worked in Excel, then Calculate Pay in Excel

Watch the Tutorial: How to Track Hours Worked in Excel + How to Calculate Pay in Excel

Decide what data to include in your time tracking spreadsheet

The first thing you’ll want to do is consider what data to include in your time tracking spreadsheet and, if you plan to share this with your client(s), how do you want for your spreadsheet to look.

In this example, we’ll use a basic layout with the following column headers:

Date
Start Time
End Time
Breaks
Total Hours
Pay Rate and
Total Pay

Headings for Tracking Hours Worked (image) — Headings for Tracking Hours Worked

Use the Right Time Formatting

Begin by formatting your Date column with your preferred date format and the Pay Rate and Total Pay columns in a currency or accounting format.

For this example, we’ll focus more on the columns with the time formatting.

Format the Start Time and End Time columns

Here, you will enter the time that you began working and the time that you ended your workday.

Key to calculating time correctly in Excel is using the right time formatting. Consider the different time formats you would need to use if calculating the number of hours you worked in a week (for example, 40 hours) versus calculating your arrival time after adding 30 minutes to your trip (for example, 9:30 AM)

*For more on this, make sure to watch our tutorial on how to format time in Excel.

The Start Time and End Time columns will both need to reflect times of a day.

The best options for these are the preset Time formats that have an AM or a PM designation or the ones in military time.

To set one of these formats, select the Start Time and End Time columns, right-click, select Format Cells and go to the Time category.

Under the Type box, you’ll see several preset time formats to choose form. Select the one you like best from those that display either a PM at the end or are in military time.

Format Start Time, End Time using a layout showing a PM at the end or one in military time (image) — Format Start Time and End Time using a layout showing a PM at the end or one in military time

For reference, my location settings are set to “English (United States)”, your options may look slightly different depending on your local settings.

TIP: When you enter your Start Time and End Times, make sure to leave a space between your number and the AM or PM. For example, type “1 PM”, not “1PM.”

Format the Breaks column

When I track my work hours, I use the Breaks column to reflect the total amount of time I was on break.

If you prefer to display the time you clocked in and out for your breaks, you’ll need to add another 2 columns like for the Start and End Times above, but for breaks.

Because I’m displaying my total break time for the day, I don’t want this column to display an AM or a PM. So, I’ll select a military time format.

Format Time for Breaks Column (image) — Format Time for Breaks Column

TIP: When you enter your Break times, make sure to always use the ( “:” ). For instance, to enter a one hour break, type “1:00” not just “1”.

Calculate the Total Hours Worked

The Total Hours column will need to display the number of hours you worked in a day, minus any breaks. You’ll then need to multiply this result by 24.

Your formula will look like this:

=(End Time – Start Time – Breaks)*24

How to Calculate Hours Worked in a Day (image) — How to Calculate Hours Worked in a Day

Why is multiplying the total time by 24 important???

Because when you select a Time format, Excel converts the time value to *look* like the time format you select. However, the value of time in Excel is actually a number equal to its percent of a 24-hour day.

For example, if you work a total of 8.5 hours that’s 35% of a 24-hour day (8.5/24).

This .35 is also the value you would see if were you to strip off the time formatting.

So, multiplying the .35 by 24 will convert your result to the 8.5 hours you worked.

Format the Total Hours column

Once you’ve set this calculation, you can now format the Total Hours column. You’ll use regular Number formatting here.

To do this, select your Total Hours column, right-click and select Format Cells. This time go to the Number category.

Format Total Hours Worked with Number Formatting (image) — Format Total Hours Worked with Number Formatting

I’ve selected to have 0 decimal places and to use the comma separator (although I doubt I’ll have that many hours!).

Click OK.

Calculate and format the Pay Rate + Total Pay columns

The Pay Rate and Total Pay columns should both be formatted with either currency or accounting formatting.

The Pay Rate column will contain your hourly fee. For example, $30 per hour.

The Total Pay column will calculate your billable amount for all hours worked that day.

You’ll multiply:

=Total Hours * Pay Rate

How to Calculate Total Pay for Hours Worked (image) — How to Calculate Total Pay for Hours Worked

Related Articles:

About Y. Acosta

Yil's (pronounced like "Jill") passion for Excel spreadsheets can be traced back to her days in marketing, where she spent significant time working on analytics. As a content creator, she now specializes in Excel spreadsheet tutorials. Make sure to visit the About page to learn more.

Leave a Reply Cancel reply