Product Release - Overtime Calculator

Overtime Calculator

Weekly & Bi-Weekly Time Cards

Designed to make entering time into Quick Books easier, this calculator prorates all time over 40 hours for every line item. This makes job costing more accurate and eliminates doing math. 

I recently built this for my own use in my construction business. Because my employees are often on more then one job in a day or have days that aren't 8 hours long, I can't just do 8 hours of regular time and the rest overtime. Quick Books does not calculate overtime. That is why I built this calculator.

Here you can see a sample of what the Time Card looks like. You can change the title to include your business name. My business uses job numbers to track divisions and separate multiple jobs for the same customer.

When using the weekly time sheet in Quick Books, the days are laid out horizontally with the initial of the weekday with the day of the month. Then all times for that day are listed vertically with a total time at the bottom. 

The layout of this spreadsheet makes it easy to tell what day of the week each line is as well as what the totals for each day are. You can see how it is calculating the overtime in the weekday totals.

When important, throughout the document instructional boxes will appear to guide you. If their location is not ideal for you, simply click and drag them to where you want them. 

In this calculator lunch is deducted from the total time. So, if your company offers paid lunches, simply do not enter any time in this column. 

The total hours calculates to the minute. Quick Books can handle paychecks being accurate to the minute.  When entering time into Quick Books, use a colon (:) to separate hours and minutes if you are entering to the minute.  If you use a decimal (.) then Quick Books converts that decimals into minutes. So, .5 = 0:30

For the weekly time card, the gross pay details are in the lower left hand corner. Enter the hourly rate you are paying your employee and the overtime rate and gross pay calculate automatically. This is a great double-check before you print the paychecks to ensure your data entry is correct.

You can overwrite the formula here if your overtime is calculated differently. 

 The Bi-Weekly Time Card has a Payroll Report. This summarizes the 2 weeks in the pay period and gives you good totals to compare with Quick Books before printing your paychecks.

Note: Be sure that your time is correct when entered into Excel before entering into Quick Books. Any changes in Excel changes every number as the ratios have changed. If you have already entered into Quick Books before the mistake is found, all of those numbers need updated. 

For my business, I built my payroll report to handle up to 7 employees for 2 weeks and up to 5 workman's comp classifications. If you are interested in having this calculator custom built to match your company, please message me for a quote.

I have provided blank time cards for your employees to fill out that match the design of the weekly time card. Use it if it would be helpful to you.

To see this spreadsheet in action click HERE

For whatever reason Etsy is not allowing Excel files to be uploaded right now, so I have this file for sale on Ebay. You can find it HERE

Thanks for checking it out. If you know someone who could use this, pass it along and don't forget to Follow By Email on the right.

Next week we will be looking at Tools - Styles Group

Nathan
Every Cell = A Calculator!

NERD ALERT!

For those of you who would be interested, I wanted to share a difficulty I came across when developing this product. When Excel calculates time it converts it into a decimal of a day. So, when I started taking my time that was to the full minute and dividing by a percentage of the week, Excel did just that. Only problem was that it was accurate in its math to the second.

It wasn't until I started imputing time into Quick Books that I discovered it was doing this. Because my time format was to the minute, Excel was rounding and my time was off 2 or 3 minutes in every week.

My solution was to use the FLOOR Function to force Excel to stay at full minutes. Now the problem was I had a few minutes that had to be added back in to get to 40 Hours. Using an array SUM inside of an IF function,

I had the spreadsheet add a minute to regular time early in the week until it reached 40 hours and then later in the week to add the minute to overtime. It only adds the minute if that day was short 1 minute after the calculations.

Regardless of if you followed that, know that it is working and that the daily totals help you confirm it is working when you compare it to Quick Books.The same is true of confirming the Gross Pay. Thanks and I will see you next week.