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
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:
- Start Time
- End Time
- Total Hours
- Pay Rate and
- Total Pay
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.
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.
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
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.
I’ve selected to have 0 decimal places and to use the comma separator (although I doubt I’ll have that many hours!).
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.
=Total Hours * Pay Rate