TIme Tracking in Excel

content:

IS Excel a quick fix for your time tracking?

It is very easy to get going with some time tracking in Excel. Just share a spreadsheet file on a central location. Create tables with weekdays and users and you are ready to go! The low entry barrier of Excel is one of the reasons that many companies start with time tracking in Excel.

Setup your time tracking in Excel

It is not difficult to set up a time tracking system in Excel, but it does require some basic knowledge of Excel. The most important things you need to know are explained below. Use this to get started with your time tracking administration!

The basics that are explained below will make it easier for you to set up time tracking in Excel. Take some time to get acquainted with the basics of using spreadsheets. Use your knowledge regularly and keep learning new things. It is important to set up your time tracking in an organized structure. This will make it easy to maintain and extend your time tracking system in the long run.

Formulas for adding up and multiplying

Keep it simple! For time tracking in Excel all you need is formulas for adding up hours and multiplying hours with hourly rates to calculate costs. Once you have created some formulas, they are very easy to apply to data on your spreadsheet.

Calculate totals in Excel

For calculating totals, Excel will do most of the work for you. As an exercise, create a vertical list with some numbers and click in the cell just below the list. Next, click on the start menu. Now click in the panel with features on the symbol that is high-lighted in yellow in the below image.

Excel creates a formula and it also selects the numbers that must be added up. Try to change the selection of numbers. When done, click on the green check mark in front of the formula bar at the top of the screen to save the formula. Excel will immediately execute the formula and add up the numbers. You can change the formula manually if you wish to.

Multiply hours with rates in Excel

Create simple formulas to multiply data. The ‘=’ character indicates the start of a formula. The ‘*’ character stands for multiply. Type for instance ‘=7*8’ in a cell. Excel will multiple the numbers and show the outcome.

To determine costs you must multiple hours with hourly rates. This goes as follows. Click in the cell where you want your total costs. Type a ‘=’ to start a formula. Next, click in the cell that contains your hours, Excel will add a reference to this cell to the formula. Now add a ‘*’ character to the formula and click in the cell with the hourly rate. Finally click on the green check mark at the top in front of the formula to complete your formula.

Apply formulas to all your data

It’s nice to have one working formula, but it is not going to do much. It must be applied to all your data and this is where Excel shines. Grab the formula and drag it down along your data. You can do this by clicking on the little square at bottom of the cell at the right hand side. Keep your mouse button down while dragging the formula down.

Extra: calculate hours from start and end time

How difficult can it be to add up hours from start time and end time. Well, there is a complication. Time and dates are expressed in a different numeric system called ‘base 12’ or duodecimal. Unlike the decimal system, which uses 10 as a base, it uses 12 as a base.
If you are going to calculate costs from time, you will have to include an extra step that converts duodecimal numbers to decimal numbers. For instance, think of how many hours there are in one-and-a half day. That would be 15 hours according to the decimal system. But in the duodecimal system it is 36 hours. You are so used to do the math in a different way when you think about time that you hardly notice.

Excel can handle the conversion between times and decimal totals. It is not difficult, but you will have to pay attention to this in your spreadsheet.

You can choose to avoid all this and simply not store any times. Often this will do, total amounts of hours is enough, no need to store start time and end time. In case you do need to register time, look carefully at the below example how it is done.

Display data in the correct format

Data must be displayed in the correct way. It is important to display numbers (more on number formats) and dates correctly. This is necessary for calculations and it looks more professional in your reports. Your numbers must have the correct number of decimal places. Also fields like dates, time and costs are usually right-aligned. Often Excel will apply the correct format automatically, but this is not always the case. Have a look at the below examples and you will not be caught by surprise!

Date Format

To display dates correctly you must change the format of the cell. Click on the cell with the right mouse button and select ‘Format cells..’, choose the correct format and click on ‘OK’. Next, drag the cell with the new format down to apply the format to all data.

Time Format

The easiest way out is to work only with hours. Sometimes it is necessary to register start time and end time. Click on the cell with the right mouse button and select ‘Format cells..’, click on ‘Time’ in the category list and select the format ‘hh:mm’. Finally, drag the cell with the new time format down to apply the format to all data.

Costs Format

Costs are expressed in a currency format. Excel has a currency format and a symbol. Click on the cell with the right mouse button and select ‘Format cells..’. If necessary you can make some changes, you can for instance leave the currency symbol by selecting ‘None’ in the drop down list for ‘Symbol’. Finally, drag the cell with the new format down to apply the new format to all the rows in the table.

Create drop-down lists

You track time on projects and activities. But you don’t want to type your project names and activity names each time you enter time. It is easier and more consistent to select from a drop-down list. If you keep this list in one place it will be easy to maintain. If you start a new project for instance, you will have to only add it once and it will be available in all sheets.

First, create a list with some example projects. It is recommended to create a new sheet in your spreadsheet for lists that are used throughout your time tracking system.

Next, go the sheet where you want to enter time. Select a cell where you want to select a project, click on ‘Data’. In the area ‘Data Tools’ select ‘Data Validation’:

Now you can specify the source data for your list. Click in the field ‘Source’ and switch to the sheet with your list of projects. The dialog box ‘Data Validation’ will stay in the foreground. In this sheet, select your list with projects. You will see a formula appearing in the field ‘Source’.

Click on OK and voilà, the list is ready. Finally, you can drag the cell down to apply the drop-down list to all rows on the sheet.

Create pivot tables for overviews

Your new knowledge will get you a long way. However, we will show you one more powerful feature of Excel. This will allow you to pivot data to create overviews. For instance to create a table with totals for each user for a list of projects.

Perform the following steps:

• Select data for your overview.
• Go to the menu ‘Insert’ and select ‘PivotTable’. The below dialog will appear. Next, click on ‘OK’ to create the overview on a new sheet.

In the next screen you will create your overview. Check the fields you want to include in the overview. Drag these fields to the boxes for filters, columns and values. Check the below example, this will create an overview with total costs for projects. You can select different projects in the overview.

Protect data against accidental changes

It is recommended to project some of your data to avoid accidental changes. In this way data like your project list will not accidentally change. In our example it is useful to protect the sheet with the project list and provide it with a password. Only an administrator that knows the password will be able to update the list.

Select the sheet with the project list and select the menu ‘Review’. Next, click on ‘Protect Sheet’ in the area ‘Protect’. Specify a password to protect the sheet.

To update the sheet, select ‘Review’ and ‘Unprotect Sheet’.

Backup your time tracking data

We all now backups are important, time tracking is a vital part of your business processes. If your spreadsheet is on OneDrive or Google Drive it is very easy to setup a backup mechanism.

But, don’t forget, a backup that has not been tested is not a real backup. Perhaps your thought you had configured a backup, but you never pressed the OK button to save your settings. To avoid this, make sure your backups work correctly by removing your spreadsheet (keep a copy handy) and restoring it you’re your backup.

Think about how long you want to keep old versions of files. If data is erased by accident and you want to get a back an old version that is too old, it might be gone forever. By default data is kept for 30 days in OneDrive. More information about configuring backups in OneDrive can be found here.

What’s next?

You have learnt how to create a time tracking Excel spreadsheet. Continue to use the techniques to learn more and improve your spreadsheet. It is not always easy, you will have to press on at times to find out how things work. You will notice that over time this gets easier. Finally, don’t forget to search on the internet, there are plenty of examples!

Time Tracking Excel Templates

There are many templates available for time tracking in Excel. All design work is done for you and you can extend the templates if needed. Try for instance this template. On the Microsoft templates site you can find many more templates. There are also companies that provide ready-to-use templates. From these companies you can order a template that is tailored towards your organisation.

Advantages of time tracking in excel

Low entry barrier

Time tracking in Excel has many advantages. Most employees have Excel available on laptop or PC. It is easy to get started with a simple setup for time tracking. This will usually suffice for starters and small companies.

Easy to Extend

Excel is very flexible. That makes it easy to add new sheets for multiple employees. It is possible to work with a small number of persons simultaneously in one spreadsheet. There is a macro language that you can use to create advanced functionality.

Trusted Software

You don’t have to buy extra software and most organisations have enough in-house knowledge to maintain a time tracking system in Excel. It is important to keep your Office setup up-to date so you always have the latest security patched installed. In this way you reduce the risk of security breaches to a minimum. In larger organisations this is usually taken care of by system administrators.

No subscription fees

Excel is no free software but most companies already have licenses for Excel in place. It is convenient to use your Office 365 subscription for your time tracking spreadsheet. Also, Office 365 also includes a backup feature.

Disadvantages of time tracking in excel

Not optimal for simultaneous data entry

It is easy to share a file with everybody and it is possible to work simultaneously with a small number of people in one file. But it is difficult to keep everybody up-to-date. If you update a file in DropBox for instance, will take some time to synchronize for all users.

The most important limitation is that Excel is not designed for simultaneous data entry. If two persons enter time in a spreadsheet in the same location, data from one user is going to overwrite data from another user, without any warnings. Google Sheets can handle this situation better, but for Google Sheets also goes that if two persons edit data in the same place, data will disappear.

Time tracking apps with a central database are much better at dealing with multiple users. These systems handle all data updates sequentially. The design of the software prevents data from one user to be updated by another user unnoticed. If someone tries to update a record while another user has changed something in the meantime, this will be detected and this will result in an error message. In this way, no data gets overwritten unnoticed.

Not user friendly

Excel is not very user friendly for quickly adding some hours in between work activities if you do not have a big screen with many files open. It is much easier to add hours on your mobile phone using a time-tracking app on the work floor.

Difficult to protect data

It is difficult to protect data from accidental changes. Setting a password after each update to a list is not very convenient. Also it is difficult to filter data for different users. Maybe not all users in your organisation should be able to see all projects. Unfortunately, there is no advanced security management for this kind of thing in Excel.

Does not scale

If your organisation grows you can extend your spreadsheet with new features and overviews. In time your spreadsheet can become very complex and difficult to maintain. This creates a risk that your data is incorrect or that you will loose data. Maintenance and repair will take more time.

Excel Administration Maintenance

Take some time to consider the risks of storing your time tracking data in Excel. It is important that all data is only accessible by authorized persons. Think about who is allowed to delete data in the cloud space you use. Accidents easily happen, it is seldom done on purpose but the damage is serious none the less.

A time tracking spreadsheet can be become very complex. Usually the knowledge of how it all works is with one or two employees. What if they leave or they get another position? It costs time and effort to maintain a spreadsheet in Excel.

project hours An alternative solution

It has become clear that Excel is a useful tool for time tracking. But Excel has its drawbacks and setup and maintenance does cost a lot of time. There are affordable alternatives that include an export of data to Excel. Read below about the advantages of time tracking with PROJECT HOURS.

Easy setup

It takes little time to setup time tracking for your company in PROJECT HOURS. You can create and remove accounts for your employees. It is easy to create projects, activities and hourly rates. You can specify which projects are visible for users.

Easy time tracking

Entering time is simple and fast, on mobile or laptop. You will only see the projects relevant for you. You can specify the amount of hours, you can enter start time and end time or you can use a timer.

View total costs at a glance

Project Hours has overviews for total project costs. For invoicing you can view a list of all time entries with start and end date for a selected project. All data can be downloaded in Excel for further processing.

Data is safe, no maintenance

Project Hours stores all data on the Microsoft Azure cloud. Third parties have no access to your data, including Microsoft. Your data is backed up, backups are kept in a secure location. All data that is transferred between you and Project Hours is encrypted.

Affordable

Project Hours costs 2 euro per person per month. This is very affordable for the ease of user and security you will get. Do not hesitate and use the long trial period of 2 months to try out Project Hours. You will see the advantages outweigh the advantages of time tracking in Excel.