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.

Fred_Pryor_Seminars_Excel_Time_Formulas_Illustration_1

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.

Fred_Pryor_Seminars_Excel_Time_Formulas_Illustration_2

What if your employees have a lunch hour? You can set up your time formula to subtract 1. See Illustration 3.

Fred_Pryor_Seminars_Excel_Time_Formulas_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.

Fred_Pryor_Seminars_Excel_Time_Formulas_Illustration_4

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.

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Related Advanced Excel Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories

13 Comments

  • Armanda says:

    Thank you! was wracking my brains. This really helped and sorted out my problems.

  • victoria says:

    THANK YOUUUU! this helped me out a lot!

  • gerranek says:

    how simple… thanks a lot!! 🙂

  • Liz says:

    This article was SUPER HELPFUL!!!! It saved me a lot of time and…frustration 🙂

  • Greg says:

    Hey,

    This article was very helpful and clear thank you.

    However, what about night work? It seems to fall down if somebody starts work at say 17:00 and finishes at 01:00…?

    Or have I set it up wrong?

    • Excel Tips and Tricks from Pryor.com says:

      In order to have accurate time measurements that cross from midnight of one day into the next, the dates must be included in the calculation.

  • Stan Giles says:

    Excel-lent! Extremely helpful. Thank you.

  • Lisa Corey says:

    I learn so much from these group. Thank you so much. It saves me so much time and headaches.

  • Kanimaran says:

    Thanks.. It’s very useful

  • Terry says:

    i am trying to use excel to work out my weekly hours, every time i hit 24 hours, my calculation reverts to 00:00. how do i fix this so it carries on calculating hours worked past 24?

    • Excel Tips and Tricks from Pryor.com says:

      We get a lot of Time calculation questions very much like this one.
      Excel uses a specific format for time calculations that exceed 24 hours.

      It’s called “Large Time” format.
      That’s important because Excel defaults to viewing all time as a fraction of a date (unless otherwise instructed)
      If all the cells (including the SUM) are formatted for “Large Time”, you can use notations that exceed 24 hours and still get a response in hours/minutes only.

  • Sharon Leslie says:

    Super helpful! Thank you!

  • Elva Carter says:

    Thanks you I am doing a time study of my duties and this really helped me fix my issues.