Efficient Time Tracking: Custom Excel Sheets for Maximum Productivity

“I don’t always Excel, but when I do, it’s at spreadsheets” – Pun-laden internet meme.

But spreadsheets are no joke. To some, they are a confusing enigma, filled with rows, columns and formulas that make them want to cry. To others, they are a way of life. Whatever group you fall into, the key is to grab spreadsheets by the cells and make them work for you. And one place where they can be very helpful is time tracking. 

What are the Pros of a Time-Tracking Spreadsheet? 

Spreadsheets allow you to:

• Easily capture, store and organize data in a consistent and structured way.
• Use formulas to calculate totals or perform repetitive tasks. No need to get your calculator out. 
• Create charts, graphs or tables to visualize, summarize, analyze, and compare data. 
• Share your data with other people. This can be done by sending a copy of the spreadsheet or by creating a live link that others can access.
• Synchronize multiple timesheets and load the results into a master spreadsheet.

What are the Cons of a Time-Tracking Spreadsheet?

Having said this, however, the process of using spreadsheets as a time-tracking log is not without its pitfalls.

Here are some of the challenges you may face: 

• Entering all the information is time consuming.
• There’s more margin for error as you’re inputting the time and data manually.
• Formulas can be unintentionally edited.
• Some employees are reluctant to fill in spreadsheets, resulting in inaccurate data. 
• You have to remember all the work you have done to complete them

As such, some people may choose to track time via a different method – say, using a time-tracking app. But if you’ve weighed up your options and decided that using spreadsheets is best for you, here’s how to get started.

Creating a Time-Tracking Spreadsheet

Begin by downloading a free Excel template. Then…

First, let’s format

In the world of time-tracking worksheets, there’s nothing more annoying than not being able to read the column headings as the word has been cut off. Rectify this issue before it’s even happened by stretching the columns horizontally, allowing for longer words.

To do this, click on cell A1 and type a fairly long word in there – something like ‘department’ or ‘spreadsheet’ will do the job. Stretch the column so the word is visible, then delete the word. To make all of the columns in the spreadsheet the same width, highlight column A, then copy (ctrl + c or command + c). Select the other columns – B down to L should be enough at the moment – then paste (ctrl + v or command + v).

Take your time

Ascertain the information you want to capture. You’ll likely use some – or all – of the following headings:

• Date
• Start time
• End time
• Lunch start time 
• Lunch end time
• Break time
• Vacation hours
• Sick hours
• Overtime
• Client
• Task/Activity
• Hourly rate

Write your headings horizontally across the spreadsheet – one in each cell. Not every company will need to track the same functions; some may need to track the work of a freelancer, while others may need to track billable hours to invoice clients. Tailor the spreadsheet to your requirements – whether you collate the data daily, weekly, monthly or per project is up to you or your company.

Clean up

Make your spreadsheet user-friendly and aesthetically pleasing. You can do this by making headings bold, adding borders, using colors, adjusting the column widths (in case they’re still not quite right having input your copy) and tweaking row heights.

The whole point of spreadsheets is to be organized, so make them look professional and you’re one step closer to achieving this goal. If you’ve created a weekly time-tracking spreadsheet, copy and paste it three times in order to create a monthly one. Likewise, if you’ve created a monthly one, copy and paste it 11 times to produce a yearly log.

Create formulas

There are various ways to write formulas to track time in Excel, depending on what you want to do with the time data. Here are a few examples:

1. Calculate the number of hours between two times
   Use the formula =END TIME - START TIME. For example, if the start time is in cell A1 and the end time is in cell B1, you can use the formula =B1 - A1 to calculate the number of hours between the two times.
2. Calculate the total number of hours worked in a day
   Use the SUM function to add up all the hours worked. For example, if the start time is in cell A1 and the end time is in cell B1, you can use the following formula: =SUM(B1 - A1). 
3. Calculate the number of hours worked in a week
   Use the SUM function to add up the number of hours worked each day. For example, if the start time is in cell A1 and the end time is in cell B1 for Monday, and the start and end times for Tuesday are in cells C1 and D1, respectively, you can use the following formula: =SUM(B1 - A1, D1 - C1) to calculate the total number of hours worked in a week.

Protect your cells

Avoid accidental ‘oops moments’ (Eek! What did I change the formula to?) by locking certain cells.

Here’s how:

• Select the cells that you want to lock.
• Right-click the selected cells and click ‘Format Cells’ from the menu.
• In the ‘Format Cells’ window, click the ‘Protection’ tab.
• Check the ‘Locked’ box and click OK.

Of course, some cells will need to be unlocked and accessible so you can actually input your hours. By default, all Excel cells on a worksheet are locked, but you can unlock them by following the same steps and unchecking the ‘Locked’ box. 

Once you have selected which cells will be locked and which will be unlocked, you’ll want to ensure they stay that way. Protect all your hard work by clicking ‘Review’, then ‘Protect Sheet’ and follow the prompts to set a password and choose which elements of the spreadsheet you want to protect.

Voila, a time-tracking spreadsheet!

TL;DR;

When it comes to tracking time in a spreadsheet, there are various pros and cons.

Pros:

• Data can be organized consistently.
• Formulas will calculate figures accurately.
• Charts, graphs and tables can be produced to analyze data.
• The data can be shared with others.
• Multiple timesheets can be synchronized into a master spreadsheet.

Cons:

• Entering the information is time consuming.
• There’s margin for error.
• Formulas can be accidentally edited.
• Some employees are reluctant to fill in spreadsheets.
• They rely on memory to a certain degree.

How to create a time-tracking spreadsheet

1. Format your columns to allow for longer words.
2. Write your headings horizontally across the spreadsheet.
3. Add things such as borders, colors and bold lettering to make your spreadsheet more user-friendly and easy on the eye.
4. Create your formulas.
5. Protect the cells, and lock/unlock them as necessary.