Eliminate Your Frustration with Excel Time Formulas!
Working with time in Excel can be a problem. Have you ever calculated how long a person was at work, and then wanted to multiply that time by an hourly wage? It isn’t difficult to fail miserably! But, if you know the trick, Excel time formulas are easy.
First, start with formatting.
- Columns A and B (employee’s Time In and Time Out) can be formatted to Time, AM/PM.
- Column C (Hours Worked) should be formatted to Time, Military. This omits the AM PM markers and creates a true figure to reflect the amount of time passed. You need a true reflection of time passed in order to work with totals accurately.
- Column D (Hours) should be formatted as a number with two decimal places.
Columns E and F should be formatted to Currency or Accounting, your choice.
Now that your formatting is set, let’s get into Excel time formula logic.
Column C contains a formula as shown in Illustration 1, =B2-A2. This calculates the number of hours the employee worked.
Column D, as mentioned, is formatted to a number, with two decimals. It contains the formula =C2*24. This time formula is so important because of how Excel is set up. Excel stores time values as decimal fractions of a 24 hour day. One hour equals 1/24th of a day. Thus, to get a whole number, you can use in calculation, you must multiply the value in column C by 24. Think of it like this. With a time like 7:30, C2 would equal 7.50, which is the number equivalent to seven hours and thirty minutes. See Illustration 2.
What if your employees have a lunch hour? You can set up your time formula to subtract 1. See Illustration 3.
Now that your formatting and time formulas are properly considered and applied, you can multiply Column D by Column E to reach the final paycheck total.
Working with time in and out of Excel, can include troublesome conversions. Using Excel with the tips listed can save a great deal of time and a few headaches as well. Next time you are adding or multiplying hours and minutes, consider using an Excel time formula.
