Overtime Calculation Values

%3CLINGO-SUB%20id%3D%22lingo-sub-1260012%22%20slang%3D%22en-US%22%3EOvertime%20Calculation%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1260012%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20time%20sheet%20that%20uses%20rows%20for%20job%20names%20and%20each%20column%20is%20a%20day%20of%20the%20week%20running%20Wednesday%20thru%20Tuesday.%20At%20the%20bottom%20of%20the%20sheet%20(C40%3AI40)%20is%20straight%20time%20(no%20overtime).%20Straight%20is%20calculated%26nbsp%3B%3DIF(SUM(C9%3AC39)%26gt%3B8%2C8%2CSUM(C9%3AC39))%2C%20only%20allowing%20a%20maximum%20of%208%20hours%20to%20be%20posted%20in%20the%20cells%20calculation.%20Time%20and%20a%20half%20is%20calculated%20in%20the%20next%20row%20(41)%20as%26nbsp%3B%3DIF(AND(SUM(C9%3AC39)%26lt%3B%26gt%3B8%2CSUM(C9%3AC39)%26lt%3B12)%2CSUM(C9%3AC39)-8%2C4).%20Row%2042%20(double%20time)%20is%20calculated%20as%26nbsp%3B%3DIF(SUM(C9%3AC39)%26gt%3B12%2CSUM(C9%3AC39)-12%2C0).%20Each%20row%20is%20totaled%20in%20column%20J40%20thru%20J42%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20the%20calculations%20from%20row%2041%20are%20not%20being%20totaled%20in%20Column%20J%20as%20expected.%20Essentially%20what%20I'm%20trying%20to%20do%20is%2C%20anything%20over%208%2C%20but%20less%20than%2012%20in%20one%20day%20is%20time%20and%20a%20half.%20So%2C%20time%20and%20a%20half%20maxes%20out%20at%204%20on%20week%20days.%20Saturday's%20are%20time%20and%20a%20half%20until%208%20hours.%20After%20that%20it's%20double%20time.%20Sunday%20is%20all%20double%20time.%20Anything%20over%2012%20hours%20is%20double%20time%20in%20row%2042.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20isn't%20time%20and%20a%20half%20being%20totaled%3F%20I%20assumed%20it%20had%20to%20be%20something%20to%20do%20with%20the%20ADD%20function%2C%20but%20I%20can't%20figure%20a%20way%20around%20it.%20Can%20someone%20help%20with%20this%3F%20My%20worksheet%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1260012%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281671%22%20slang%3D%22en-US%22%3ERe%3A%20Overtime%20Calculation%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F598459%22%20target%3D%22_blank%22%3E%40boodahbellie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20Time%20%26amp%3B%20Half%20formula%20returns%20negative%20values%20for%20some%20days%2C%20and%20applied%20to%20these%20cells%20numbers%20doesn't%20show%20negative%20numbers%20and%20zeroes.%20You%20may%20change%20applied%20format%2C%20or%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMAX(0%2CMIN(SUM(C9%3AC39)-8%2C4))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20both.%3C%2FP%3E%0A%3CP%3EDidn't%20catch%20what%20it%20shall%20be%20in%20next%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

I have a time sheet that uses rows for job names and each column is a day of the week running Wednesday thru Tuesday. At the bottom of the sheet (C40:I40) is straight time (no overtime). Straight is calculated =IF(SUM(C9:C39)>8,8,SUM(C9:C39)), only allowing a maximum of 8 hours to be posted in the cells calculation. Time and a half is calculated in the next row (41) as =IF(AND(SUM(C9:C39)<>8,SUM(C9:C39)<12),SUM(C9:C39)-8,4). Row 42 (double time) is calculated as =IF(SUM(C9:C39)>12,SUM(C9:C39)-12,0). Each row is totaled in column J40 thru J42 respectively.

 

My problem is that the calculations from row 41 are not being totaled in Column J as expected. Essentially what I'm trying to do is, anything over 8, but less than 12 in one day is time and a half. So, time and a half maxes out at 4 on week days. Saturday's are time and a half until 8 hours. After that it's double time. Sunday is all double time. Anything over 12 hours is double time in row 42.

 

Why isn't time and a half being totaled? I assumed it had to be something to do with the ADD function, but I can't figure a way around it. Can someone help with this? My worksheet is attached.

1 Reply
Highlighted

@boodahbellie 

That's since Time & Half formula returns negative values for some days, and applied to these cells numbers doesn't show negative numbers and zeroes. You may change applied format, or formula like

=MAX(0,MIN(SUM(C9:C39)-8,4))

or both.

Didn't catch what it shall be in next row.