How do I create a tracker in Excel?

Clockify, Toggl, Harvest, and many other apps provide different sophisticated features for time tracking. Excel is not a time tracker, but you can use it as one. For this, you’ll need to build a time-tracking sheet with specific columns & rows, labels, formatting, and formulas. It’s not rocket science, but it will take some time. Read our guide to learn how to create a custom time-tracking spreadsheet in Excelб or enjoy the ready-to-use template for your needs.

Table of Contents hide

Excel time-tracking spreadsheet explained

Why use Excel spreadsheet for time tracking

How to make an Excel weekly time tracking spreadsheet

Basic info block

Activities tracker block

Totals block

Monthly Excel time tracking spreadsheet template

Basic info block

Activities tracker block

Totals block

Export data from a time tracking app to an hours time-tracking Excel spreadsheet

Synchronize a billing app with a time-tracking Excel spreadsheet

Bonus: Excel spreadsheet for tracking multiple employees time on OneDrive

Drop-down list in an Excel spreadsheet for tracking employees’ time off

Calculation formulas for the employee time tracking Excel spreadsheet

Should you use Excel to create a paid time off tracking spreadsheet?

Excel time-tracking spreadsheet explained

A regular timesheet contains cells where you enter time records, as well as the cells containing formulas to calculate the total hours worked, including over- and under-time. Optionally, you can specify details about employee(s), project, manager, deadlines, etc. Here is an example of a basic timesheet:

The essence of this document is to keep track of time records that can be used for payroll, billing, project status report, and other purposes.

Why use Excel spreadsheet for time tracking

Let’s be honest, Excel is not a competitor to time-tracking apps. They are user-friendly, out-of-the-box, feature stuffed, and so on. But they also cost money. If your team is not too large (up to 15 teammates) and Excel is your common spreadsheet application, using it for time tracking is a good option for a few reasons:

• You can customize a time tracking spreadsheet however you want.
• You can synchronize multiple timesheets and load the results into the one master spreadsheet.
• You don’t need to export time records from a time tracker to share it with stakeholders.

Your specific project can receive more benefits by using Excel for tracking time. Now, let’s get our hands dirty and learn how you can build a time-tracking Excel spreadsheet yourself.

How to make an Excel weekly time tracking spreadsheet

To build a custom time tracker in Excel, first you need to decide which sections are required for your needs – for example, start and end date, time in and out, total hours worked, rate per hour, etc. For our weekly time-tracking template, we chose the following blocks:

• Basic info (project name, name of an employee, start and end dates)
• Activities tracker (day of week, name of activity, time in and out, etc.)
• Totals (total hours per week, over- or under-time, and total earned)

It’s also crucial to use proper formatting for different values in your spreadsheet. Let’s review each block separately and explore the details.

Basic info block

This is what comes first in our DIY time-tracking Excel spreadsheet. Create a worksheet and fill out a few cells with information about your project, range to track time spent, as well as your name or the names of your employees/teammates. Optionally, we added a display of current time using the NOW function Excel, as follows:

It’s pretty useful when you need to enter the time in or out. To choose a desired format for your time values, right click on the cell and click Format cells…

Then select the format for the time values you want to see.

Let’s move on to the next block.

Activities tracker block

Now, the main course. The activities tracker is the block where you will input your time entries manually or automatically. For our tracker, we specified the following fields:

• Day of the week 
• Activities
• Time in
• Time out
• Total
• Total per day

Days of the week

Since we’re building a weekly tracker, we’ll group the rows by the days of the week – Monday, Tuesday, etc.

Tip: In Excel, you can enter Monday in one cell and drag it down to fill out the other cells with the consequent values.

Activities

In this column, you will enter the names of the activities you’re going to track. If you have a predefined set of activities, you can specify all of them in a column on a separate worksheet and link those to your Activities column as a drop-down list. In this case, you’ll be able to select the necessary activity from the drop-down. 

To do this, select the range, go to the Data ribbon => Data Validation = Data Validation…

Select List as a validation criteria and enter or select the range with the values to be added to the drop-down list. Click “OK“.

Now you can select the necessary activity from the drop-down list.

Time in and time out

In these columns, you’ll be entering the start and end time for your activities. Make sure that these columns are formatted for time values, for example, like this:

Total 

In the Total column, we will calculate the total hours spent for a particular activity using the following formula:

=IF(D12>=C12,D12-C12,D12+1-C12)*24

• =SUM(E12:E17)

  1 – a time out value

• =SUM(E12:E17)

  2 – a time in value

Drag this formula to the end.

Note: Alternatively, you can use the array formula,

=SUM(E12:E17)

3, like we did for the Excel spreadsheet for tracking multiple employees’ time on OneDrive.

Make sure to select the Number format for the cells in the Total column.

Note: We can’t use a regular subtraction formula,

=SUM(E12:E17)

4, because in this case, we would have an incorrect return if the time in and out values are not for the same day. For example:

Total per day

In the column Total per day, we will calculate the time spent for a whole day. For this, we selected the cells related to a particular day of the week and merged them.

Then we applied the following sum formula:

=SUM(E12:E17)

Drag the formula down for the other days of the week.

Totals block

In the Totals block, we will have inputs such as Total hours per week, Rate per hour, Total earned, as well as Over-/Under-time. 

Total per week

To calculate the total hours per week, we need to sum total hours per each day. Here is the formula we used:

=SUM(F12:F36)

Total earned

The total earned is the multiplication of total hours per week and rate per hour, so the formula is rather simple:

=F6*F7

Overtime/Undertime

This value should be beneficial for employees to track their work hours progress. For this, we added an expected hours field where you can specify the value – B8 cell. The over-/under-time is the subtraction of the expected hours from the total hours per week,

=SUM(E12:E17)

That’s it! Your weekly time-tracking Excel spreadsheet is ready for use. Download a copy of the spreadsheet template to your computer. 

Monthly Excel time tracking spreadsheet template

Weekly time sheets are less in demand than monthly ones. So, let’s edit our spreadsheet to meet this time range.

Download a copy of the monthly time tracking spreadsheet Excel.

Basic info block

In addition to start and end dates, we added a current date field, which rests on the TODAY Excel function:

=TODAY()

For the end date field, we used the EOMONTH function:

=EOMONTH(B4,0)

Now, it will display the end of the month automatically once you specify the start date.

Another change to the Basic info block is the Expected hours per month. We implemented the calculation with the help of the NETWORKDAYS Excel function:

=NETWORKDAYS(B4,F4)*8

The formula will automatically calculate the total expected working hours considering the 8-hour working day. 

We also relocated the Rate per hour field to this block and added a field where you can specify the number of days off or vacation days. This number needs to be subtracted from the expected hours. So, let’s update formula for Expected hours as follows:

=NETWORKDAYS(B4,F4)*8-B9*8

• =SUM(E12:E17)

  6 – number of days off

Activities tracker block

The Activities tracker block did not experience many changes. It just gained two columns, date and project, and lost the total hours per day.

Totals block

In the Totals block, we replaced Total per week with Total per month and edited the calculation formula. The Overtime/Undertime field was also updated considering the fact that now it means the difference between the Total hours and the Expected hours per month.

Export data from a time tracking app to an hours time-tracking Excel spreadsheet 

Above we said that Excel is not a competitor to time trackers. Nevertheless, teams who switch from a time-tracking spreadsheet to a time-tracking app may keep using both tools. Why? They may need to share their time records with stakeholders, analyze employees performance, and other things. So, they can export records from their trackers to a spreadsheet app, Excel or Google Sheets, either manually or automatically.

Manually means that you need to download a CSV or Excel file. It’s an easy and pretty fast option, unless you have to do this regularly, such as every week or even every day! For example, here is how it works in the Harvest tracking app.

And here is what the exported data looks like in Excel.

After the export, you’ll need to play with this data to make it visually understandable for the stakeholders. And you’ll have to do this again and again with every data export. So, it’s better to automate the flow.

Synchronize a billing app with a time-tracking Excel spreadsheet

You can automate export of data from Clockify, Harvest, and other billing apps to Excel on a schedule you want. For this, you need to sign up to Coupler.io. It’s a data-transferring app that lets you take data from multiple sources and load it to Excel, Google Sheets, or BigQuery. 

All you need to do is perform three steps:

• Set up source 

Configure the connection to your billing app, including the selection of the data entity to export, period, and other parameters. For example:

• Set up destination 

Choose Excel, connect to your Microsoft account, and select the workbook and worksheet to load data to.

• Set up schedule

Enable the automatic data refresh and configure the frequency for your scheduled data exports. 

When you click “Save and Run“, the data will be imported into your worksheet. The data refresh will be implemented according to the chosen schedule. So, you can be sure that your report or dashboard with your time entries will be up to date.

Bonus: Excel spreadsheet for tracking multiple employees time on OneDrive

As a bonus, we decided to share an Excel time-tracking spreadsheet that can be used by several employees. 

Download a copy of this spreadsheet.

The logic is the following:

• The spreadsheet is stored on OneDrive and accessible by three employees: Henry Spencer, Charles O’Shee, and Danny Oulsen.
• They can enter their time records in this spreadsheet randomly. 
• The total hours per month, as well as the total earned amount, will be calculated automatically per each employee.

For this, we added an employee column with a drop-down list to the Activities tracker block and updated the calculation formulas. 

Drop-down list in an Excel spreadsheet for tracking employees’ time off

To create a drop down in Excel Online, select the range of cells, go to Data => Data validation, and select List in the Allow field.

Now you need to specify a range that contains an array of values to be returned in the drop-down list. We created a separate worksheet named “params” and added two columns there: Employee and Rate. For the drop-down, we only need the Employee column.

Click “OK”.

Calculation formulas for the employee time tracking Excel spreadsheet

To calculate total hours per activity, we update the current formula with the following array formula:

=IF(F14:F100>=E14:E100,F14:F100-E14:E100,F14:F100+1-E14:E100)*24

This means that there is no need to drag the formula down.

The total per month calculation rests on Excel SUMIF, which allows us to select a criteria for totaling values. 

=SUMIF(B14:B294,B7,G14:G294)

The criteria, B7, is the name of the employee, which is selectable from a drop-down list.

The Rate per hour field also changed. Since we have three employees with different rates, we used the Excel VLOOKUP function to return the rate according to the chosen employee. 

=SUM(E12:E17)

Should you use Excel to create a paid time off tracking spreadsheet?

For the final word, let’s answer the question that you may have after reading the post: Is Excel good for time tracking? It is if your team is not big and you already leverage Excel as a spreadsheet app for your analytics. 

However, bigger squads with 20+ teammates should definitely look at a time-tracking app. At the same time, they can easily benefit from the synergy of both Excel and a billing app with the help of Coupler.io. Make a choice that’s right for you, and good luck!

How do I add a tracker in Excel?

Does Excel have a task tracker?