How to Track Time in Excel: A Step-by-Step Guide

New to time tracking? 

Not sure what’s the best way to track time for your team?

But what if the solution was already at your fingertips?

Enter Excel, a tool you already have access to. It is simple enough to track time before switching to time tracking software.  

In this article, we’ll guide you through setting up a timesheet in Excel, formatting it and adding functions correctly, and explaining the benefits and limitations of using Excel to track time.

A quick note: Excel, though free and simple to track personal time, doesn’t work well for teams. Compiling timesheets and making sense of time data can quickly become a chore for managers. Instead, try using one of our recommended best time tracking apps, many of which have a free version.

But if you want to still use Excel, here’s how we’d do it.

How to track time in Excel?

You can use a Microsoft Excel spreadsheet to track your time by creating a timesheet. We’ve covered the steps below 👇

Step 1. Decide what to track

Take the time to figure out what data you want to track in your timesheet. 

You can get as granular as you like here with the following options to record:

• daily work hours
• in and out times
• in and out times along with break start and end times

Step 2: Format your spreadsheet

When you first open a new Excel file, the cells are rather small. This can make it difficult to read your Excel timesheet easily without clicking on individual cells.

So, before you do anything, select column A on the left side of your screen and drag it to the width you want. Then, right-click the column and select copy.

Next, you highlight the column range from B to the last column you’ll need in your timesheet, depending on the information you wish to include. We recommend including at least until column L.

Now, hit CTRL+V to paste the formatting to all the selected columns.

Step 3: Add a title to your timesheet

Select the cells from A1 to L1, then click Merge & Center in the toolbar under the Home tab. Now, you can enter the name of your timesheet in the resulting cell.

You can name the sheet how you want, but we recommend using your company name.

Once the text is added, center-align it and format it how you like. You can change the size, font, and color to fit your brand, but it’s a good idea to make it bold to stand out.

Step 4: Create basic information labels

To ensure people know the correct places for data entry, you need to add labels to your timesheet.

The exact labels you add will depend on the data you’ve chosen to track and the specifics required by your company, but some general ones most timesheets need are:

• Employee name
• Manager/reports to
• Department
• Timesheet month

Space the labels out evenly across your timesheet columns so it looks something like this (we used cells A2, A4, F2, and F4):

Once your labels are added, click the Wrap Text icon so the words all fit in one cell. It looks like this:

Next, you can create a space for the employees to type in. This is especially important if you plan on printing the timesheet. To do this, highlight the cells next to the cell where you added the Employee label (in our case, B2 to D2). Select the Bottom Border option from the borders drop-down next to the font format buttons in the taskbar.

Doing so will add a line at the bottom of the selected cells. Merge the cells and have the perfect space for your employees to input their information.

Repeat these steps for each of the other labels you’ve added until your spreadsheet looks like this:

Step 5: Add time-related labels

Next, you need to add labels to indicate where employees should add their time-related information, like day, date, time in, and time out.

First, divide the employee and time-related details by merging all the cells in the first row underneath the employee information. In our example, it would be A6 to L6.

Start inputting your time-related labels in the next row to create column headings.

We’ve added:

• Day in A7
• Date in B7
• Time In in C7 and E7 (to show when the employee clocked in for the day and clocked in after lunch break)
• Time Out in D7 and F7 (to show when the employee clocked out for lunch break and clocked out at the end of their workday)
• Total Hours in G7
• Overtime Hours in H7
• Sick Hours in I7
• Vacation Hours in J7

Feel free to customize these labels to fit your company’s needs. For example, you can add extra Time In/Time Out sections for breaks or remove them altogether.

Now that your labels are added, you can format them to your liking. Start by filling in the row with your color of choice by highlighting the row and selecting Fill Color from the toolbar. Make the text bold so it stands out, Wrap It, and center it in the middle of the cell.

Select All Borders from the Borders tool drop-down. Then add the days of the week in the first column on the left under the “Day” label, and your timesheet should look a bit like this:

Step 6: Add some final touches

Formatting your cells in the timesheet for readability is a good idea to ensure all your cells are fully legible.

Start by selecting all the rows where time entries will be entered. In our example, it would include rows 8 to 14

Drag the bottom edge of any row to make all the cells wider, then, with the same rows highlighted, select Center Text and Align to Middle.

Next, select the cells that form part of the time tracking sheet, like so:

Apply All Borders to make boxes around the cells so your sheet can be read easily.

Lastly, tidy up your spreadsheet by removing any unnecessary borders or colors in cells you won’t use, make your final adjustments to fonts, colors, and formats, and you’re done!

Tada! 🎉

It’s a little time-consuming to create time tracking spreadsheets in Excel or Google Sheets from a fresh worksheet. That’s where templates come in, and we’ve gathered some for you in our next section 👇

NB:  Click here to download the Excel timesheet template we created in this section.

Save time with these ready-made Excel time tracking templates

Using a timesheet template to create your Excel timesheet can save you a lot of time and effort — especially when it comes preloaded with formulas to tally up your hours and bill for the week.

Take a look at these free, ready-made Excel time tracking templates from Toggl Track 👇

Daily timesheet template

This template is super simple. It breaks the day down into 30-minute slots, where freelancers or contractors can input the tasks they’ve carried out over their workday.

Simply enter an hourly rate and fill in the blanks.

Download this daily timesheet template here.

Weekly timesheet template

This timesheet template includes spaces for start time, end time, and break duration for a full week.

Click here to download the weekly template.

Biweekly timesheet template

This timesheet template is the same as the one above but works for two weeks instead of one. You can also add your overtime hours to this sheet.

Download the biweekly timesheet here.

Monthly timesheet template

Lastly, we have the monthly Excel timesheet template from Toggl Track. Here, regular hours and overtime hours for each day of the month are added, along with the billable rate.

Click here for the monthly timesheet template download.

Advanced tips and tricks for tracking time in Excel

We’ve given you the tools to start tracking your time in Excel, but advanced functions, like calculating total hours, multiplying them by your rates, and applying formulas to your data, can take your Excel time tracking game to the next level. 

Here are some top tips that will kick you up a gear.

How to calculate totals in Excel

To calculate totals in Excel, such as for your total hours worked in a week or month, click in the first empty cell under the numbers you wish to tally, Then click the home menu and click this symbol 👇 

Excel will automatically create a SUM formula to calculate the total number of hours from the list. Still, you can manually edit the formula if it’s missing something or adding cells you don’t need.

When you’re done, click the green check mark or hit the enter key.

Alternatively, you can manually enter the formula into the empty cell by typing =SUM followed by the cell range you wish to total in brackets, separated by a colon. So, for our example below, we would enter =SUM(B6:B12) like this 👇

Hit enter, and you’re done.

How to multiply hours with rates

To calculate how much to invoice, you must multiply your total hours by your hourly rate. 

To do this, select the cell where you want the result of your formula to appear. Type the = symbol followed by the cell with your total hours, then the * symbol, then the cell that has your hourly rate, like so:

Hit the enter key, and Excel will automatically populate the cell with the amount you should invoice.

How to apply formulas to your data

You’ll probably need more than one Excel formula in your spreadsheet to tally up your hours and calculate your total costs.

To do this quickly for multiple rows, input the formula into the first cell, then click the small square at the bottom right corner and drag it down to the last cell where you need your formula, like this:

Boom! Now, your timesheet is filled with formulas.

How to calculate hours from start to end time

If you add start and end times to your timesheet instead of the specific hours worked, you’ll need to figure out the hours from the times.

Now, bear with us, as this can get a little complicated.

Firstly, times and dates are expressed using a different numeric system called duodecimal or base 12 instead of the decimal system we’re used to, where 10 is the base.

To calculate costs from time in your Excel timesheet, add an extra step that converts the duodecimal time entries into decimal numbers.

Excel expresses an hour in the duodecimal system as 1/24 in the decimal system, or 0.0416666666666667. So, to calculate the decimal version of your hours, you need to divide the hours in duodecimal by 0.0416666666666667.

Start by subtracting the start time from the end to give you your hours in duodecimal format. Type the “=” character, the cell where your end time is entered (say C7), the “-” character, then the cell where your start time is entered (say B7) to give you the formula =C7-B7.

Next, make sure the column where you wish to show the hours in decimal is formatted to General—this is very important; otherwise, the formula will not work. For our example, we’ll use column E.

Once the cell is formatted, you need to enter the formula that will divide the hours in the duodecimal cell (D7 in our example) by 0.0416666666666667. Type in =D7/0.0416666666666667 then hit enter, and you should see the decimal version of your hours.

Finally, click and drag the small box in the lower right corner of the cell to copy the formula to the rest of the cells in the base 12 column.

Note: This can be avoided if you can fill in your timesheet with the hours only instead of the exact times. However, if your company requires start and end times, this formula will make your billing calculations a breeze.

How to display data in the right format

As we discussed in the previous section, how your cells are formatted to display data is vital.

While Excel mostly applies the correct format automatically, you may need to check and edit the format to ensure everything displays correctly.

Here are some common examples 👇

Date format

Right-click the cell you want to format and select format cells. Choose the correct format and click OK.

Drag the cell with the new format to apply the formatting to all the cells you want to include.

Time format

Right-click your chosen cell, select Format cells, and then click Time.

Select your chosen format from the list, click OK, then click and drag the cell to apply the format over your selected cell range.

Costs format

Right-click the cell, select Format cells, then click Currency from the category list.

Now, you can edit the format according to your needs by changing the decimal places, currency symbol, and how negative numbers are displayed.

Once again, click OK, then click and drag the cell to copy the format to the other cells you need it in.

What are the benefits of tracking time in Excel?

Tracking time in Excel is simple once your timesheet is set up. It’s a basic, no-frills approach to time tracking, but it is effective.

There are a few benefits of using Excel to track your time:

• You can track time data at the granular level of your choosing, customizing the timesheet however you want.
• There’s no need to export time records to share with stakeholders; simply share the timesheet link.
• You can synchronize your team’s timesheets into a master spreadsheet.
• If you’re already using Excel as your default spreadsheet application, there’s no extra cost involved — no matter how large your team is.
• It’s easy to use, and little to no learning curve or onboarding is needed once your timesheet is created.

Despite these benefits, there are some drawbacks.

What are the limitations of tracking time in Excel?

Excel time tracking is useful, but it is quite an outdated approach.

Simply put, there are better methods of employee time tracking as Excel time tracking has several limitations, such as:

• Time data must be entered manually. This can be time-consuming and introduces the potential for human error.
• Excel doesn’t integrate with many tools, meaning you can’t connect it to your project management tools, meaning more manual work and flicking between different software to see your data.
• No automatic reports make it difficult to find patterns or gain valuable insights into how employees spend their time.
• The timesheet can easily be broken if employees accidentally delete formulas or formatting.
• It’s not an ideal approach for large teams.

These drawbacks and limitations might have put you off using Excel to track time. But what are the alternatives?

Is there a better way to track time than Excel timesheets?

Yes! Instead of using Excel to track time, you can use a time tracking tool like Toggl Track to track employee time better, faster, and smarter.

Here are some of Toggl Track’s features that you won’t get with Excel:

• One-click timer: Start or stop time tracking at the click of a button so you have accurate time data down to the second.
• Calendar integration: Sync your tracked time with your calendar for a comprehensive schedule view.
• Detailed reports: Gain deep insights into your and your employees’ work habits with reports that are customizable and available automatically.
• Project and task tracking: Add tags to your time entries, assign them to specific tasks or projects, and mark them as non-billable or billable hours for better project management.
• Team collaboration: Easily share reports and track time for better coordination.
• Built-in Pomodoro timer: The tool’s Pomodoro timer breaks work into focused intervals—there is no need to use a secondary timer.
• Offline tracking: Track time even if you’re not connected to the internet.

Tracking time with Toggl Track, one of the best time tracking apps, couldn’t be easier. There are different ways you can do it:

• Using the running timer: When the page is set to Timer mode, click the pink play button or press N on the keyboard to start the timer.
• Using Manual Mode: Use Manual Mode to add entries for activities before or after they happen.
• In Calendar view: Add entries by clicking empty slots in the calendar and then clicking the Play icon.
• Continue a previous entry: Simply hover over an existing entry and click the continue button or press C on the keyboard.
• Input duration: In Timer mode, click the duration field and input a number.

It’s your turn now

Setting up a timesheet in Excel is an effective way to track employee time on a small scale, but it can be tedious and time-consuming, with a high risk of inaccuracy.

However, more time tracking tools like Toggl Track can help automate time tracking and reporting, eliminating the need for manual work and reducing the chances of human error.

Ready to see how Toggl Track can streamline your time tracking workflow? Sign up for free today.

Frequently Asked Questions (FAQs)

Still have a few questions? We’ve got you covered below👇

How do I automatically count time in Excel?

Simply input a formula that deducts the start time from the end time to the cell where you want the answer to appear.

For example, if your start time is in cell A2 and the end time is in cell B2, your formula would be =B2-A2.

How do I add hours and minutes in Excel?

You can use the SUM function to calculate the total of hours and minutes. 

For a range of cells, say D1 to D5, you can add the formula =SUM(D1:D5) to the cell where you want the answer.