10 things you need to know about time tracking in Excel [Free template]

Excel for time registration and corporate management

Many companies use Excel for time registration. Typically, each employee has their own spreadsheet for time registration containing a tab for each week. Each tab holds Mondays through Sundays with fields for entering events and time registrations. Spreadsheets like these are usually more or less automated as regards flex, allowances and travel expenses.

Spreadsheets are then typically saved to a company server for monthly data consolidation prior to invoicing. This consolidation is either performed manually or automated via macros. Using linked spreadsheets, data is retrieved from weekly timesheets or other intermediary sheets and analyses are generated into a report spreadsheet. The possibilities are almost endless when it comes to Excel. Here’s our top 10:

10 benefits: Excel for time registration

• You can customise a number of variables using “if” formulas and specific macros, thereby retrieving the exact data you want.
• All data are saved locally on a corporate network drive.
• Efficient pivot tools are presented in clear table form.
• Your data is always right at your fingertips.
• Useful if you have consistent hourly rates.
• Copying data to other spreadsheets or systems is easy.
• Can be used for small projects and events with limited time registration requirements.
• Reporting features (one of the things for which Excel was originally developed).
• Extended functionality using Excel’s macro tools.
• Excel is part of the Office package, a standard for many companies, making it the obvious choice.

Excel: the land of opportunity

Excel is often used in companies with as many as 30 employees. These companies develop smart solutions for integrating Excel with their financial systems, email systems, etc., and voilà: Excel has become the production system for the entire company! Microsoft is undoubtedly thrilled by this, and if you ask the CEOs how Excel came to play such a pivotal role in the company, the answer is usually that they started out using Excel when the company was established.

Furthermore, Excel is included in the Office package, it’s highly flexible and it can be adapted to meet requirements such as certain data structures and analysis dimensions. Also, the Excel-based business system can be further developed by CFOs without the need for software developers and stringent standard-system frameworks.

The fine print: what Excel doesn’t do

Once a company nears 25 or 30 employees, the Excel era is coming to an end. What was once the obvious choice for time registration, corporate management and reporting has grown into a costly solution in the long run. Time registration errors are easily made, and quality-assuring all these data is becoming increasingly costly – not to mention determining who’s in charge of this crucial task. Ten minutes off a project isn’t much, but what if it happens three times? That’s half an hour wasted.

As the company grows along with its needs for complex calculations, the disadvantages of Excel become increasingly apparent. As such, there are a number of areas in which Excel does not shine. We’ve listed them here:

10 reasons why excel is losing ground to other business systems

• Overwriting formulas and lines. Once data have been overwritten and changes have been saved, recovering data without a change log is almost impossible. You can insert comments in Excel, but do your employees remember to use this function, let alone know of it?
• Deleted spreadsheets. Sad but true. Often, deleted files on the corporate network drive can be difficult to recover, making deletions all the more disastrous. Are you prepared to leave such important data in one file – which can be wiped away at the mere click of a Delete button?
• Incorrect formulas. Incorrect sum calculations leaving out key figures, gross miscalculations or incorrectly syntaxed formulas. Excel’s pitfalls are as legion as its possibilities.
• Adaptation. Adapting columns and cell data is difficult if the scope of the project changes significantly or if time registrations require moving or editing. One result is a project schedule in Excel based on correct underlying data but in which task changes or budgets, project structures or scopes are difficult to manage due to mutually dependent formulas.
• Log feature. Is inserting comments in each cell a sound solution in terms of consistency? A system which automatically tracks changes and registers by whom they were made saves precious time when troubleshooting data. Without it, one time registration error might take weeks to uncover.
• Analysis features. Surprisingly, the weakest link in this construction may have been the sole reason for choosing Excel in the first place: analyses are based on certain groupings of raw data, making it difficult to expand data structures and, in particular, choosing new angles for analysing existing data.
• Inaccuracy and reconstruction of time registration data. How do you secure a sound basis for your data? Often, when using Excel for time registration, we find that registrations are made on a weekly or monthly basis. But by using a business system offering different time registration clients suited for different tasks, the chances of achieving more precise time registrations and, consequently, a higher invoicing potential are improved, as billable hours are not forgotten. The shorter the interval between time registration and project events, the more precise the invoicing.
• Can you see what is invoiced? Who’s in charge of ticking off invoiced hours in the main spreadsheet? And where do you select which hours have been booked as revenue – but not for long-term projects? In these cases, one of the main benefits of the business system is that already invoiced items will be marked as such, eliminating the risk of double invoicing. Likewise, completed work booked as revenue is clearly marked and must by default be approved by the project manager before being marked ready for invoicing.
• User rights management. Who has access to which data? Granted, you can password-protect your files or save the main spreadsheet to another location. However, your data are still vulnerable, as an accidental click of the Delete button can wipe away all your hard work.
  Business systems offer user rights management, ensuring that project managers have access only to the features they need. Meanwhile, project managers can easily mark time registrations for invoicing and easily redistribute employee hours. If project managers do not have access to the employee’s own spreadsheets, redistributing project work hours can be a time-consuming affair, e.g. if the project manager needs to update a large central spreadsheet with complex consolidation formulas.
• Stage of completion. For fixed-price projects, finding out why an overrun has occurred can prove difficult if no comments have been created. And in connection with customer disputes, data such as comments can be crucial to securing indisputable invoice documentation.

There are no easy decisions

But for today’s national and international organisations, access, retrieval and reporting systems for managing key data can mean all the difference.

Can you afford to lose tenders to your competitors because you have yet to define the following:

• Your average hourly rate for fixed-price projects too
• Your most profitable customers
• Your budget status – in hours as well as money – for all current projects
• How to distribute your working hours to optimise your resources
• Your actual employee workload for projects and events

These are just some of the points you should be considering – and you need to know if Excel can help you define them. Should you convert from Excel to an actual business system or not? There are no easy decisions; after all, Excel is highly diverse application which has probably proven invaluable for many years. Should you decide that the time has come to upgrade to a business system, however, we’ve put up a few blog entries which we hope will help you make an informed decision.

• 100 tips for your requirement specifications
• Pros and cons: project management in Excel

We’ll show you the way

We can help YOU import business data from huge spreadsheets into TimeLog. If you’re interested, book a free online presentation of the system, and find out how we can help you keep track of time.