# calculating overtime

#### harveya915

##### Board Regular
So I have a spreadsheet to keep track of my time at work. It goes In - Out, In - Out, Total (time I clock-in in the morning then out at lunch then back in after lunch and out for the day).

My total time formula for the day is represented as =SUM(B1-A1)+(D1-C1). My cell format is [h]:mm and so on goes down the sheet for the rest of the pay period which is 2 weeks (total of 14 days).

My total time formula for the weeks is represented as =SUM(E1:E14). My cell format is [h]:mm (Cell E15)

Then I have my hourly wage cell format as "General" (Cell E16)

I have the format of cells set up with [h]:mm because I do not want a decimal returned as the value. I want the full time displayed. In the overall total hours it will give me the exact hours with the exact minutes, not a decimal.

I am using an "IF" function to figure out my total wages for the week (Cell E17), but for some reason I can't get it to work. This is what I have so far:

=IF(E15,<=80,(E15*E16*24),((E16*80)+((E15-80)*(E16*1.5)))

The "True" value breaks down: (E15)Total Hours * (E16)Wage * 24
The "False" Value breaks down: (E16)Wage * 80 hours "base pay" + (E15)Total Hours - 80 hours "calculates hours that qualify for overtime because obviously the false value would mean that Total Hours worked is greater than 80" * (E16)Wage * 1.5 "calculates overtime rate".

Any suggestions? Thanks!

Last edited:

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

##### Well-known Member
Hi Harveya915,

I note a spurious comma at the beginning of your IF statement but you don't explain your error, so is that it?

Book1
ABCDE
19:0012:0013:0018:008:00
29:0012:0013:0018:008:00
39:0012:0013:0018:008:00
49:0012:0013:0018:008:00
59:0012:0013:0018:008:00
69:0012:0013:0018:008:00
79:0012:0013:0018:008:00
89:0012:0013:0018:008:00
99:0012:0013:0018:008:00
109:0012:0013:0018:008:00
119:0012:0013:0018:008:00
129:0012:0013:0018:008:00
139:0012:0013:0022:0012:00
149:0012:0013:0021:3011:30
15119:30
1610
171,195.00
Sheet1
Cell Formulas
RangeFormula
E1:E14E1=SUM(B1-A1)+(D1-C1)
E15E15=SUM(E1:E14)
E17E17=IF(E15<=80,(E15*E16*24),((E16*80)+((E15-80)*(E16*1.5))))

#### harveya915

##### Board Regular
Good eye on that extra comma!
Unfortunately this still doesn't work. The false formula is supposed to take the excess mount of hours over 80, multiply those hours by the the wage of "time and half". So I figured in my formula: E15 119:30 - 80 should equal 39:30 hours. Then the other half of the formula is E16 Wage of 10 * 1.5 equals 15. The resulting calculation should be 39:30 * 15 = 592.50. Then this answer gets added to the beginning of the formula where E16 wage * 80 hours = \$800 + \$592.50 = \$1392.50.

The "true" statement works fine, it's the "False" statement that is giving me problems.

Hope this makes sense.

Last edited:

##### Well-known Member
Ah! Of course Excel holds time as a fraction of a day so we'll need to convert to decimal hours for the calculation (even if you don't want to see them) so we'll need a few multiply by twenty-fours.

Book1
ABCDE
10:000:000:000:000:00
20:000:000:000:000:00
39:0012:0013:0018:008:00
49:0012:0013:0018:008:00
59:0012:0013:0018:008:00
69:0012:0013:0018:008:00
79:0012:0013:0018:008:00
80:000:000:000:000:00
90:000:000:000:000:00
107:0012:0012:3023:0015:30
117:0012:0012:3023:3016:00
127:0012:0012:3023:3016:00
137:0012:0012:3023:3016:00
147:0012:0012:3023:3016:00
15119:30
1610
171,392.50
Sheet1
Cell Formulas
RangeFormula
E1:E14E1=SUM(B1-A1)+(D1-C1)
E15E15=SUM(E1:E14)
E17E17=IF((E15*24)<=80,((E15*24)*E16),((E16*80)+((E15*24)-80)*(E16*1.5)))

• harveya915

#### Dave Patton

##### Well-known Member

T202008c.xlsm
ABCDEF
109:00:0012:00:0013:00:0018:00:0008:00:00
209:00:0012:00:0013:00:0018:00:0008:00:00
309:00:0012:00:0013:00:0018:00:0008:00:00
409:00:0012:00:0013:00:0018:00:0008:00:00
509:00:0012:00:0013:00:0018:00:0008:00:00
609:00:0012:00:0013:00:0018:00:0008:00:00
709:00:0012:00:0013:00:0018:00:0008:00:00
809:00:0012:00:0013:00:0018:00:0008:00:00
909:00:0012:00:0013:00:0018:00:0008:00:00
1009:00:0012:00:0013:00:0018:00:0008:00:00
1109:00:0012:00:0013:00:0018:00:0008:00:00
1209:00:0012:00:0013:00:0018:00:0008:00:00
1309:00:0012:00:0013:00:0022:00:0012:00:00
1409:00:0012:00:0013:00:0021:30:0011:30:00
15119.50119:30
1610.0010.00
171,392.501,392.50
1f
Cell Formulas
RangeFormula
E1:E14E1=(B1-A1)+(D1-C1)
E15E15=SUM(E1:E14)*24
F15F15=SUM(E1:E14)
E17E17=IF(E15<=80,E15*E16,(E16*80)+(E15-80)*E16*1.5)
F17F17=IF(F15<=3.33333333333333,(E15*24*F16),(80+(F15-3.33333333333333)*24*1.5)*10)

• harveya915

#### Dave Patton

##### Well-known Member
T202008c.xlsm
ABCDEF
109:00:0012:00:0013:00:0018:00:0008:00:00
209:00:0012:00:0013:00:0018:00:0008:00:00
309:00:0012:00:0013:00:0018:00:0008:00:00
409:00:0012:00:0013:00:0018:00:0008:00:00
509:00:0012:00:0013:00:0018:00:0008:00:00
609:00:0012:00:0013:00:0022:00:0012:00:00
709:00:0012:00:0013:00:0021:30:0011:30:00
863.5063:30
910.0010.00
10635.00635.00
1f
Cell Formulas
RangeFormula
E1:E7E1=(B1-A1)+(D1-C1)
E8E8=SUM(E1:E7)*24
F8F8=SUM(E1:E7)
E10E10=IF(E8<=80,E8*E9,(E9*80)+(E8-80)*E9*1.5)
F10F10=IF(F8<=3.33333333333333,(F8*24*F9),(80+(F8-3.33333333333333)*24*1.5)*10)

• harveya915

#### harveya915

##### Board Regular
Ah! Of course Excel holds time as a fraction of a day so we'll need to convert to decimal hours for the calculation (even if you don't want to see them) so we'll need a few multiply by twenty-fours.

Book1
ABCDE
10:000:000:000:000:00
20:000:000:000:000:00
39:0012:0013:0018:008:00
49:0012:0013:0018:008:00
59:0012:0013:0018:008:00
69:0012:0013:0018:008:00
79:0012:0013:0018:008:00
80:000:000:000:000:00
90:000:000:000:000:00
107:0012:0012:3023:0015:30
117:0012:0012:3023:3016:00
127:0012:0012:3023:3016:00
137:0012:0012:3023:3016:00
147:0012:0012:3023:3016:00
15119:30
1610
171,392.50
Sheet1
Cell Formulas
RangeFormula
E1:E14E1=SUM(B1-A1)+(D1-C1)
E15E15=SUM(E1:E14)
E17E17=IF((E15*24)<=80,((E15*24)*E16),((E16*80)+((E15*24)-80)*(E16*1.5)))

Worked like a charm! I knew it had something to do with the "24"s but just didn't know where to fit them in. Thanks for all your help!

#### Dave Patton

##### Well-known Member
What is the purpose of your Sum function in E1:E14?
Did you try the suggestions in #5 or #6
Cell G1 has the time 80 hours.
You can name the cell G1 or use a constant with the 80 hours.

T202008c.xlsm
ABCDE
109:00:0012:00:0013:00:0018:00:0008:00:00
209:00:0012:00:0013:00:0018:00:0008:00:00
309:00:0012:00:0013:00:0018:00:0008:00:00
409:00:0012:00:0013:00:0018:00:0008:00:00
509:00:0012:00:0013:00:0018:00:0008:00:00
609:00:0012:00:0013:00:0018:00:0008:00:00
709:00:0012:00:0013:00:0018:00:0008:00:00
809:00:0012:00:0013:00:0018:00:0008:00:00
909:00:0012:00:0013:00:0018:00:0008:00:00
1009:00:0012:00:0013:00:0018:00:0008:00:00
1109:00:0012:00:0013:00:0018:00:0008:00:00
1209:00:0012:00:0013:00:0018:00:0008:00:00
1309:00:0012:00:0013:00:0022:00:0012:00:00
1409:00:0012:00:0013:00:0021:30:0011:30:00
15119.5
1610
171,392.50
18or
19119:30
2010
211,392.50
22or1,392.50
1f (2)
Cell Formulas
RangeFormula
E1:E14E1=(B1-A1)+(D1-C1)
E15E15=SUM(E1:E14)*24
E17E17=IF(E15<=80,E15*E16,(80+(E15-80)*1.5)*E16)
E19E19=SUM(E1:E14)
E21E21=IF(E19<=3.33333333333333,(E19*24*E20),(80+(E19-3.33333333333333)*24*1.5)*10)
E22E22=IF(E19<=G1,(E19*24*E20),(80+(E19-G1)*24*1.5)*E20)

Replies
1
Views
66
Replies
4
Views
82
Replies
26
Views
3K
Replies
76
Views
2K
Replies
1
Views
44