How Using A Spreadsheet For Time And Attendance Ended In Disaster
The Company We Keep
I run a small copywriting agency. We have eight part-time employees who work from home. “Home,” in this case, includes diverse places around the country. We even have a couple people clocking in from overseas.
For the most part, we work during regular business hours. This gets a little complicated when you factor in our various time zones. Some of us begin work at eight a.m. three hours before others clock in, …also at eight a.m.
This time zone shift added some complexity we didn’t expect. It became one of the contributing factors to our future disaster.
We also maintain a flexible work schedule among a few of our employees. This means that while we try to maintain a regular 9-5 workday, some of us are breaking off at odd hours of the day.
Flexible work hours are important to us, but this convenience adds complexity. In any given day, any employee can clock in and out many times and in variable chunks of time.
We developed a system of tracking time and attendance with a series of spreadsheets. It seemed like a good idea, and it even worked for a while.
Soon I discovered that my simple system was actually creating more complexity. It opened the door to disaster…
Setup And The Time And Attendance Speculation
My first step was to devise a plan for tracking our time and attendance. I decided that we would keep a master spreadsheet to track everything.
Each employee would keep their own personalized version of the spreadsheet. On it, they would track their own daily time and attendance.
At the end of each pay period, employees would send their time and attendance spreadsheets to me for recording.
I considered using a shared online spreadsheet in Google Docs. The problem with using a shared spreadsheet is privacy. We needed to keep records separate and private, so individual spreadsheets were necessary.
My first crack at creating a usable spreadsheet earned a revision in the first week. I speculated on what fields we would need and started with what I thought was a good set. It wasn’t long before I was getting calls from employees asking where to log special items.
For example, I included job codes so we could track which project was getting which hours. What I forgot to consider was job roles and the variable costs associated with them.
I had to revise the spreadsheet in the first week to include our various job roles. I also added a complicated macro to calculate the pay scales for each. Hurray -I was now a developer, too!
This, of course, wasn’t the last time I would revise our spreadsheet time and attendance system. It seemed that I was in a constant state of discovery. Every other payroll a new item would come up, and I would add it to the spreadsheet.
This is one of the problems with using a spreadsheet. I was gaining a complex working knowledge of time and attendance. I also had to figure out how to add complex calculations and verify totals.
It wasn’t long before time and attendance tracking was taking over my life. I spent way too much time revising, distributing, and updating spreadsheet files.
I added a mountain of complexity to my routine but was yet to realize the full extent.
Procrastination And The Payroll Catch Up Game
Once we got the spreadsheet tuned up, I was ready to settle into spreadsheet bliss. This never happened, and I am here to announce that there is no such thing. At least in time and attendance.
I learned pretty fast that I don’t have the time to collect time and attendance. This is a problem because my employees consider this the primary reason they hang out with me all day.
While they work to rack up billable hours, I worry about operations, sales, and marketing. There’s not a lot of time left in the day for tracking time and attendance, so I let it slip.
To clarify; I realize this is of paramount importance. The problem is that I get to the end of my day and figure I’ll get to it in the morning. By the time morning is gone, I’m already running too fast to turn back, and push it to the next day.
Of course, when I get to the end of the payroll period it’s time to pay The Piper. So my routine became an endless loop of procrastination, a half day catchup session, and an empty promise to keep up the next time.
During my catch up sessions, I’d have to backtrack to correlate records. In the event that an employee was doing the same thing, we’d have to coordinate our memory of their work history. This happened more often than you could imagine.
The result of this cumbersome spreadsheet system was a disaster. I was doing all the work in a single dedicated afternoon. Much of what I was recording was from memory, and sometimes second hand.
I was in a constant routine of catching up, and I knew there were things I was missing. To keep things running smooth, I cobbled together our time and attendance each week the best I could.
I figured that was enough. I was wrong. It took more time than I expected and paved the way to disaster. There were several times I worked well into the night to meet our payroll deadline.
There has to be a better way, I would think to myself. But then I’d finish up and forget the whole thing until the end of the next period. Here we go again.
Errors And The Great Time Card Migration
It was on one of those weary late night catch up sessions that I learned another big lesson. Actually, it wasn’t during my catch up session. It was three days later when an employee called me to report a problem.
Apparently, her check didn’t match her records. She was short on her payment and was understandably a little upset.
I love my employees, and the last thing I want to do is short them for the hard work they contribute. So what happened?
My first instinct was the right one. I transposed a number when I typed in the information from her spreadsheet report to my master file. This was a troubling discovery. I wondered how many times I had done this. My mind started spinning out to all the possibilities:
I could be making errors that underpay my employees. This is bad for them when they catch it. Oh, and by the way, “catching it” can be in six months when we do taxes, which means records are out of sync.
To fix the problem we had to add tricky entries into our books that are just begging for an audit. But it gets worse…
Transposition errors can also favor the employee. These might not get reported, and in the end, I end up paying more than I planned, which can be challenging to the budget.
Worse still, this kind of error puts the business in a compliance pickle. Federal regulations come into play when time and attendance mistakes are made. Fines can be imposed. Penalties can be levied. And bad blood can be generated between you and your employees. It ain’t pretty.
Apparently transposing numbers during a manual data transfer is a common problem. I learned that businesses can get into a lot of trouble when this becomes a chronic problem.
Correlation And The Kitchen Sync
I don’t want to use the words “cooking the books,” but that’s what it can look like when you are manually tracking time and attendance. When you get into a manual routine of playing catch up, you have to fill in a lot of blanks with information that isn’t there.
Employee interviews, follow-ups to review time cards, and adjustments here and there was the norm. We relied on memory more than often, and I gave the benefit of the doubt at the expense of the company.
Correlating data between my master spreadsheet and my employee time sheets became an untamable monster. It took an enormous amount of time and I could never be sure it was accurate. There were too many manual links in the chain.
Synchronizing time and attendance data with a spreadsheet system is incredibly time-consuming. It also creates a lot of opportunities for errors to enter your system. Errors that can come back to bite you pretty hard, as I learned…
Conflict And The Liability Problem
This all came to a crashing realization at the end of our fiscal year. I wrapped up our reports and sent them to each of my employees.
One of my employees performed her own audit. She noticed an error that went back through half of the entire previous year.
Because of her time zone, she was working at odd hours. She also had several obligations through the day which complicated her work schedule. Many of her time cards were broken into many segments of work and her time and attendance were all over the map.
In the end, it appeared we had shorted her, but there was a conflict over just how much. Our records didn’t match, and procrastination introduced holes on both sides of the argument.
As it turns out, the Department of Labor has an interest in problems such as this. So does the local state labor commission. I was suddenly in a bit of hot water, and as the employer, all eyes were on me to make this right. I was shocked to learn that there could be devastating fines and even jail time involved.
Fortunately, a complete disaster was avoided and I was able to work things out with my employee.
Regardless, this was a pretty significant shock to the system, and I knew we had to make some changes right away. I’ve seen the light and it scared me to death.
Time to find some workforce management software to replace my spreadsheet system. The good news is that I discovered it wasn’t very expensive, did more than I expected, and solved a lot of problems. I don’t know why I didn’t do this in the first place.
Restarting and The Lesson Learned
Once I had the employee time and attendance situation resolved, it was time to restart. No more spreadsheets for me; this was taking too much time, and was too risky. I needed a better time and attendance tracking solution.
As it turns out, SwipeClock has a time and attendance solution. Not only did it solve my problems, it made life better for me in ways I had never contemplated.
For starters, Swipeclock offers a cloud-based time tracking solution. Each of my employees can log in from wherever they are and track their time from their browser. This is not only convenient, but it addresses the security issue we had with spreadsheets.
Another tremendous advantage of online time tracking is accuracy. Each of my employees has a simple clock in or clock out option in their browser. The system takes care of time stamps and calculations. If an employee forgets to clock in or clock out, they get an alert to remind them. No more working from memory!
Perhaps the best part about having employees track their own time in a cloud-based workforce management system is the verifiable record keeping. Everything is automated, so I don’t have to track records from day to day. It’s all stored in the cloud, so I don’t have to worry about making backups or double-checking security.
And if there is ever a need for an audit, I have accurate records at my fingertips that will be hard to dispute.
Employees have a self-service portal that allows them to keep track of their own time. This is like a running audit that allows employees to double check time and see their history. This reduces the chances that employees introduce errors that go undetected.
I have an administration panel that allows me to see where we stand in real time. I can see if we are approaching unplanned overtime problems, and I even get alerts when this is a possibility. It’s like having an all-knowing guardian to watch my back.
I no longer have to spend mountains of time correlating reports. Time and attendance reports are automatically recorded and synced. I can even import the records directly into payroll without having to type a single number. No more transcription errors for me.
The system practically takes care of itself, and all I have to do is review reports and click to export to payroll. The system has my back when it comes to unplanned overtime. Employees are empowered to maintain their own records and have instant access to their own history. All in a secure environment that meets Department of Labor compliance requirements.
There are unseen benefits, too. For example, we no longer have to worry about updating to the latest software. SwipeClock cloud-based workforce management software is always up to date. We don’t have to worry about compliance or legal requirements because they are automatically built into the system. I can rest easy knowing that I didn’t miss something when I set up the spreadsheet.
SwipeClock workforce management software has replaced our spreadsheet-based time and attendance solution. And let’s be frank; in comparison, our spreadsheet wasn’t really much of a solution as much as it was a failed experiment. I’m glad we’ve moved on; things are a lot better, and that’s the lesson we’ve learned.
By Cary Snowden
Simplify HR management today.
International HR Day 2022: Shaping the New Future As a provider of HR solutions, we love honoring hardworking HR professionals. International HR Day is a perfect opportunity to do just that. This year, the theme of International HR Day is “Shaping the New Future.” “We are leading a future that will be more inclusive, flexible,…Read More
Since a small business HR system has a lot of moving parts, you need an organized process to make an informed choice. An HRIS evaluation template can help. But first, let’s start with the basics of HRIS systems What is an HRIS? A Human Resources Information System (HRIS) is a type of business software. It…Read More