calculating overtime

harveya915

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

Some videos you may like

Excel Facts

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
991
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 4, 2015
Messages
95
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:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
991
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,338
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,338
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Sep 4, 2015
Messages
95
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
Joined
Feb 15, 2002
Messages
4,338
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,865
Messages
5,542,956
Members
410,579
Latest member
bdubz
Top