Excellent management skills begin with talking to yourself. It’s not crazy: it’s an exercise in time tracking excellence. Which brings us to the big question of the day:
You may be thinking: “What a ridiculous question. We need time cards to track time, duh!” But this is a very important question to ask before you create a time card or decide on a time tracking software.
Here’s how we break it down.
What are your time tracking needs?
Do you need . . .
Today we start with the first things first: how to build a time card in Excel. For some, this will be all they need to manage their employee time cards. For others, it will be a good review on the importance of time cards. We’ll also throw in some suggestions for those who need a little more umph with their time cards.
Sure our time card tutorial is awesome, but you don’t have to start from scratch. Download our Time Card Excel Template to get back to the work you love. Our time card template allows you to compare your hours planned vs hours worked — just like Bric.
Here is our 4-step quick and dirty process for building a time card in Excel. Use it, change it, share it, whatever you need.
Step One: Gather information. Time cards are obviously for employees. What do you need to know about your employees? Name, ID #, social security number, department, and job title perhaps?
Next, talk to payroll. What is the pay period? Weekly, bi-weekly, monthly, etc? You’ll need to know what dates to include before you organize information in Excel.
Step Two: Organize information. Put the employee information at the top of the time card. Set up columns vertically for each day of the pay period. Enter column horizontally for the hours worked that day. They can be broken up into morning and afternoon if you want to visually exclude lunch breaks, but that means the employees will have to log in to track time at least twice a day.
Step Three: Automate addition. If you chose to break up the day into morning/afternoon, then you’ll need to automate the subtotal for the hours worked that day. The formula for addition in Excel is =SUM(A4+B4). In this case, A4 and B4 are stand-ins for the names of the morning and afternoon cells on the time card. This will obviously vary.
You also need to automate the grand total. This is the total hours worked during the pay period of the time card. Use the same SUM formula, like this =SUM(C4:C11). The colon in the equation means through. Thus, the formula adds all of the subtotals for each day to create the grand total of the pay period.
Fun Excel fact: when you paste the SUM formula into other rows, it automatically changes the formula to have the new cell names so you don’t have to manually change it.
Step Four: Lock your new time card. To prevent tampering from owly employees, you need to lock certain cells in your Excel time card, and you need to lock the entire time card too.
To lock certain cells, such as the subtotal and grand total cells, you actually need to unlock the others. Confused? Excel automatically locks all the cells. To unlock the cells where the employee enters her hours, go under the “Protection” tab, hit “format cells,” and uncheck the “locked” box.
To create a password for the time card, go to “Format,” then “protect sheet” then hit the “select unlocked cells” button. It will then prompt you create a password, and then VOILA: your very first employee time card. Quite the milestone.
The truth is that Excel time cards can be a little fancier, but it’s a bit of a headache. It’s possible to automate the dates so you don’t have to re-enter them manually every time period. You can use formulas to calculate overtime, designate “in” and “out” columns, account for sick and vacation time, and more
Proficiency in Excel is a merit claimed by most professional Americans. Thus, your employees may appreciate the simplicity of an Excel time card. The difficulty falls on the person programming the time card. It takes a lot of patience to set up fool-proof formulas that can stand a lot of users at once. Once it’s set up, however, it’s straight forward in execution.
Remember when we told you that time tracking needs a new purpose? What we meant was that time cards shouldn’t be just about punching numbers for payroll. Time cards are useful tools for data collection, too. Excel time cards make it difficult for employees to see the data-side of time tracking.
Here is a list of why you may want to consider a time tracking software instead of a time card in Excel.
Time tracking software exists that is a collaborative and data-centered, project-oriented and filled with visualization. With Excel time cards, you’re stuck punching numbers. You can’t unlock the real benefits of best time tracking practices for your creative agency.
There is Bric. Bric does time cards for you, and presents time tracking information on a visually pleasing dashboard for the gaze of all employees. To track time, employees simply click the project they are working on, and the time begins. When they’re about to go overtime, they receive a warning. They can also check on the time progress of other employees and projects, all in one simple software. When projects are finished, Bric presents data about project times, budget, and employee optimization to the users. There’s no digging for information, it’s all right there.
Say goodbye to late night headaches programming time cards in Excel. Click here for a free 30-day trial of Bric.