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-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

May. 19

Central Time Zone

May. 20

Eastern Time Zone

May. 25

Eastern Time Zone

May. 26

Central Time Zone

Jun. 2

Central Time Zone

Jun. 7

Eastern Time Zone

Jun. 8

Central Time Zone

Jun. 10

Pacific Time Zone

Jun. 14

Central Time Zone

Jun. 16

Central Time Zone

Jun. 17

Eastern Time Zone

Jun. 21

Central Time Zone

Jun. 22

Pacific Time Zone

Jun. 24

Eastern Time Zone

Jun. 29

Eastern Time Zone

Jul. 7

Pacific Time Zone

Jul. 8

Central Time Zone

Jul. 12

Eastern Time Zone

Jul. 13

Central Time Zone

Jul. 15

Central Time Zone

Jul. 19

Pacific Time Zone

Jul. 21

Central Time Zone

Jul. 22

Eastern Time Zone

Jul. 26

Eastern Time Zone

Jul. 27

Central Time Zone

Jul. 28

Central Time Zone

Aug. 2

Pacific Time Zone

Aug. 4

Central Time Zone

Aug. 5

Eastern Time Zone

Aug. 10

Central Time Zone

Aug. 12

Central Time Zone

Aug. 16

Eastern Time Zone

Aug. 17

Central Time Zone

Aug. 19

Pacific Time Zone

Aug. 24

Central Time Zone

Aug. 26

Pacific Time Zone

Aug. 30

Eastern Time Zone

Sep. 1

Central Time Zone

Sep. 8

Central Time Zone

Sep. 13

Central Time Zone

Sep. 14

Pacific Time Zone

Sep. 16

Eastern Time Zone

Sep. 20

Central Time Zone

Sep. 22

Central Time Zone

Sep. 23

Eastern Time Zone

Sep. 27

Pacific Time Zone

Sep. 28

Central Time Zone

Sep. 30

Central Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

May. 19

Eastern Time Zone

May. 24

Eastern Time Zone

May. 25

Central Time Zone

Jun. 1

Central Time Zone

Jun. 6

Eastern Time Zone

Jun. 7

Central Time Zone

Jun. 9

Pacific Time Zone

Jun. 13

Central Time Zone

Jun. 15

Central Time Zone

Jun. 16

Eastern Time Zone

Jun. 20

Central Time Zone

Jun. 21

Pacific Time Zone

Jun. 23

Eastern Time Zone

Jun. 28

Eastern Time Zone

Jul. 6

Pacific Time Zone

Jul. 7

Central Time Zone

Jul. 11

Eastern Time Zone

Jul. 12

Central Time Zone

Jul. 14

Central Time Zone

Jul. 18

Pacific Time Zone

Jul. 20

Central Time Zone

Jul. 21

Eastern Time Zone

Jul. 25

Eastern Time Zone

Jul. 26

Central Time Zone

Jul. 27

Central Time Zone

Aug. 1

Pacific Time Zone

Aug. 3

Central Time Zone

Aug. 4

Eastern Time Zone

Aug. 9

Central Time Zone

Aug. 11

Central Time Zone

Aug. 15

Eastern Time Zone

Aug. 16

Central Time Zone

Aug. 18

Pacific Time Zone

Aug. 23

Central Time Zone

Aug. 25

Pacific Time Zone

Aug. 29

Eastern Time Zone

Aug. 31

Central Time Zone

Sep. 7

Central Time Zone

Sep. 12

Central Time Zone

Sep. 13

Pacific Time Zone

Sep. 15

Eastern Time Zone

Sep. 19

Central Time Zone

Sep. 21

Central Time Zone

Sep. 22

Eastern Time Zone

Sep. 26

Pacific Time Zone

Sep. 27

Central Time Zone

Sep. 29

Central Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

May. 20

Central Time Zone

May. 23

Central Time Zone

Jun. 3

Central Time Zone

Jun. 8

Eastern Time Zone

Jun. 14

Eastern Time Zone

Jun. 16

Central Time Zone

Jun. 22

Central Time Zone

Jun. 23

Pacific Time Zone

Jun. 27

Eastern Time Zone

Jul. 6

Central Time Zone

Jul. 12

Pacific Time Zone

Jul. 13

Eastern Time Zone

Jul. 18

Central Time Zone

Jul. 19

Eastern Time Zone

Jul. 28

Eastern Time Zone

Jul. 29

Central Time Zone

Aug. 2

Eastern Time Zone

Aug. 9

Pacific Time Zone

Aug. 12

Central Time Zone

Aug. 17

Eastern Time Zone

Aug. 18

Central Time Zone

Aug. 22

Central Time Zone

Aug. 25

Eastern Time Zone

Aug. 31

Pacific Time Zone

Sep. 9

Central Time Zone

Sep. 14

Eastern Time Zone

Sep. 15

Pacific Time Zone

Sep. 19

Central Time Zone

Sep. 27

Eastern Time Zone

Sep. 28

Central Time Zone

Related Advanced Excel Articles

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. 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.
      reply
      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.
      reply2

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