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.
13 Comments
Thank you! was wracking my brains. This really helped and sorted out my problems.
THANK YOUUUU! this helped me out a lot!
how simple… thanks a lot!! 🙂
This article was SUPER HELPFUL!!!! It saved me a lot of time and…frustration 🙂
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?
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.
Excel-lent! Extremely helpful. Thank you.
I learn so much from these group. Thank you so much. It saves me so much time and headaches.
Thanks.. It’s very useful
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?
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.
Super helpful! Thank you!
Thanks you I am doing a time study of my duties and this really helped me fix my issues.