Utilizing Excel: Schedules and Automation



Service and product-based companies share many similarities as well as differences. For this article, we are going to focus on one specific requirement that service-based companies need. The goal of a service company is to spend most of their time working for clients to cover costs and turn a profit while leaving enough time to develop and grow the organization. There are plenty of products available that give these companies a way to manage how much clients should be billed. It’s up to the employees to keep track of when they work on different projects; the owner decides how much to bill clients for that time. That is why it is imperative to have the ability to keep track of billable and non-billable hours.

Keeping Track of Time: Using Excel

This concept is neither new or exciting, but it is necessary. A product-based company has a concrete object that shows the productivity of the business. When you provide services, it is difficult to measure your productivity level. Once you develop a system to measure your hours, how do you measure whether those hours are billable or not? You could sit and write down every single hour and a description of what you performed. This sounds incredibly tedious and wasteful of valuable time that could be better spent serving clients and generating revenue.

The goal that I wanted to reach with this spreadsheet is automation. There are several approaches out there; scheduling templates are available from within Excel and on their online resources but none of these could provide the automation that I was looking for. A schedule that automatically calculated how long I’d spent on a project so that when I entered my time into my timesheet I wouldn’t have to go back and count each hour for each project. This wastes time, especially in cases where I worked on a project multiple times during the day. The tedious counting was not conducive to a productive day.

I decided to solve my own problem by developing an Excel spreadsheet that would calculate the different number of hours that I had spent on the various projects.

Development of an Automated Schedule

I created this sheet using different Excel functions most of which are relatively simple:

  • Basic Functions:
    • IF
    • AND
    • OR
    • VALUE
    • ROW
    • SUM
    • MAX
  • Conditional Formatting: Entirely for cosmetic purposes. In fact, this spreadsheet could probably do with a lot more beautifying, but presentability is not the purpose of my schedule.
  • PivotTable: To separate the projects into a list that then counts how many occurrences appear throughout my day. This is important because I have separated the day into 15-minute intervals and I used the autofill feature to show how long I worked on a project.
  • Developer mode to create two buttons that make my life slightly easier:
    • Clear List: Empties my list of projects for the day
    • Refresh PT: Updates the PivotTable since it does not automatically update when you change your data.

I have spent a lot of time on this spreadsheet, probably more than I should have: going through multiple iterations, trying different solutions, and researching what functions could perform the actions I wanted. However, it does save me a few minutes at the end of every day and it’s easier than trying to remember what I did all day.

True Benefits of Scheduling Automation

After developing this schedule, I realized that efficiency and productivity was not the real benefit. Building this spreadsheet has given me a project to work on during my downtime and is something that I can take pride in. I built this spreadsheet on my own through my own creativity and effort. No one asked me to build it, it has a useful purpose, and it serves as a demonstration of what I’m capable of making. Not to mention, this spreadsheet makes me happy. I enjoy working with Excel and seeing what I can do with it. I’ve always had an interest in computer programming despite being an Accountant. Unfortunately, I am a terrible programmer. Luckily, Excel acts as a middle ground between a clean user experience and the dirty nuts and bolts behind software.

It’s important for everyone to find a hobby or something at work they look forward to. Yes, you should like your job, but having something to do on the side allows you to take a break when you’re stressed while still being somewhat productive. In my case, I was able to use this schedule to keep my brain working during my downtime and have something that I liked doing. Even though I currently am at a point where I don’t know what else to do with it, I am sure there are hundreds of improvements I could make, even if it’s just to the appearance. That gives me a lot of hope about where this spreadsheet could lead me. It will never be finished, and nothing is ever perfect, which means I will always have something to reach for.

If you’d like to learn more about how this schedule works, please let me know and I can create a follow-up post where I get into how each function works and why I made certain decisions along the way. I challenge you, however, to find your own way to solve this problem and see what you come up with. You might just surprise yourself.