Excel Overtime Calculation

Question

I need to calculate the number of hours and mins I have gone over 1 hour/60 mins. So if I did 1 hour and 6 mins of something and i write that in one cell (E9) I want the other cell (G9) to show 6 mins. So it shows I did 6 mins over the required 1 hour. If I did 2 hours and 40 mins (E9) I want the cell (G9) to show 1 hour and 40 mins.

Does that make sense? I'm trying to have one cell show the actual time worked (E9) and the other cell show me how many mins and/or hours i went over the standard 60 mins/1 hour.

Please let me know the format the cell should have also. I'd rather see something like (1:40) to indicate 1 hour and 40 mins but if its easier to have it say 100 mins instead that's fine too. I just dont want anything that says like 1.38 hours. I need to know actual mins.

I also want it to show that if i'm under 1 hour, it shows how many mins i'm under it. So if i only did 40 mins (E9) it will show -20 mins in (G9)

Can anyone assist me?

E9 is the cell that houses the time i actually completed

G9 should house the time i went over an hour.

Answer 1

A quick way to do this with just minutes (i.e. 100 min) would be to write as the contents of G9:

= E9 - 60

If you instead want to display the time in hours and minutes, type the time in hours and minutes like you want, and make sure the cell format in G9 is set to Custom, [h]:mm, and set the value of G9 to:

=A1-1/24

Since Excel works with dates and not times, 1/24 is one hour.

Answer 2

Excel doesn't play nice with time as a quantity, in my experience. It really only deals with it in terms of actual points in history. So you'll want to simply use two formulas, both of which divide your original number by 60, to get what you're after:

  | Minutes Worked | Hours:Minutes Worked | Hours:Minutes Over 1 Hour
      100                 1:40                  0:40

  Column 1: Data entered by you, time worked in minutes
  Column 2: =QUOTIENT(A1,60)&":"&MOD(A1,60)
  Column 3: =QUOTIENT((A1-60),60)&":"&MOD(A1,60)

Those formulas should work. Column two finds the hours worked by taking the value in column 1 and dividing it by 60, and then rounding down (FLOOR) to get rid of the remainder. It then finds the minutes by doing the opposite. It takes the value entered and divides by 60 and returns the remainder (MOD). Since the minutes left hour the hours are always going to be 0-59, you get your minutes.

The second column does the exact same thing, but it subtracts 60 (1 hour) from column 1 first.

Both of them separate the values of the hours and minutes with ":" by simply placing it in between the two values.

If you want to find the sum of all of this overtime, you just need to add a SUM formula underneath the first column, and then use the exact same formulas from the second and third column to convert that some to your desired format.

Update:

First, I changed the above code, replacingFLOOR with QUOTIENT, since Floor throws errors when the number is below 1.

Second, if you want to have a negative number for when you work under an hour, you can use a conditional that says that when minutes worked is > 59 to use the formula, but otherwise to subtract 60 from the number of minutes difference from 60 (MOD). I would do it the other way around to get the proper negative number, but then it looks like 00:-20 for when you work 40 minutes, and who wants that? Instead we manually add the -00 in the front.

And since we're doing all that, I figure you want "00" instead of just "0" for when you have amounts like 0:30 minutes over or 5:0 hours over. So that can be done by wrapping the entire number (the hours number or the minutes number, not the entire cell) in a TEXT(number, "00") function. The end result is hairy but looks like:

  Function for finding total hours:minutes from minutes
 =TEXT(QUOTIENT(A1,60),"00")&":"&TEXT(MOD(A1,60),"00")

  Function for finding hours:minutes (positive or negative) over first hour:
  =IF(A1 > 59,TEXT(QUOTIENT((A1-60),60),"00")&":"&TEXT(MOD(A1,60),"00"),"-00"&":"&TEXT(60-MOD(A1,60),"00"))

Answer 3

Try if the following is sufficient for you:

Three columns (using A,B,C here) defined as:

A ["hh:mm"]  B [Standard]  C ["hh:mm"]

A: Your values

B: =IF(B1<1/24,"-","")

C: =ABS(B4-"1:00")

Results in:

01:06     00:06
02:40     01:40
00:40   - 00:20

As the minus is in an extra cell, you can not directly continue calculation with these values though.