Overtime Calculation Values

Copper Contributor

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.

6 Replies

@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.

@SERGEI Hope you are well, can you tell me what formula to use if the value is more than 8 hours for a day off but I can only deduct 8 hours for day off (normal working hours are only 8 hours) thnx

@Janedb 

Is that for single cell of for aggregation or what? Perhaps you could provide very small sample Excel file with manually added desired result. In general you may use MIN(8, value) to deduct.

I tried =Min(8,(b15-a1) which returns 15 instead of 8

@Janedb 

In this case

=MIN(8/24,B1-A1)

Time in Excel is decimal part of the number, e.g. 01:00:00 = 1/24. please check in attached.

Thank you Sergei. :)