calculating overtime

H

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
141
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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))))
 
Upvote 0
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:
Upvote 0
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)))
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
Toadstool said:
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)))
Click to expand...

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!
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
673
HyraxWithAFlamethrower
H
HappyChappy
  • Question
Payslips
2
Replies
17
Views
356
Logit
Logit
T
  • Question
Timesheet For Building My Own Home For Tax Purposes
Replies
8
Views
158
etaf
etaf
J
  • Solved
VBA or Excel – Time sheet with RT / OT based on the day of the week.
Replies
5
Views
239
awoohaw
awoohaw
M
  • Solved
How do you calculate a conditional sum for conditional formatting?
Replies
9
Views
228
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,521
Messages
6,120,018
Members
448,937
Latest member
BeerMan23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top