Indicating where a range has overtime hours in a range of cells
 

Hi there

Hoping you can help. I'm a newbie to this forum, hope below makes sense

In summary I have a spreadsheet where I log technician hours as well as holidays and sick days

I have the date and day of the week in AA1, AA2 (entered as WEEKDAY(AA1,1) and AA3 is where I log the hours worked

By row:
Hours are entered over a 14 days period and run from Sunday to Saturday
At the end of 14 days there are 5 cells that indicate the:
- sum of basic hours worked - (AO)
- Time and Half hours - (AP)
- Double Time - (AQ)
- Sick days - (AR) - catered for with formula of =COUNTIFS(AB3:AF3,"*sick*")+COUNTIF(AI3:AM3,"*sick *")
- Holidays - (AS) - as above

What I can't figure out is what formula to put in AO:AQ that will sum:

Where Mon to Fri entered in AA3:AN3 =<8 then total in "Basic Hours" (AO)
Where Mon to Fri in AA:AN is 8 < 10 then 8 in "Basic Hours" and upto 2 hrs in "*1.5" (AP) cell
Where Mon to Fri in AA:AN is 10 hrs then 8 in "Basic Hours" (AO), 2 in "*1.5" (AP) then rest in "*2" (AQ)
Where Saturday hours AA:AN first 8 hours in "*1.5" (AP) then balance in "*2" (AQ)
Where Sunday AA:AN all hours in "*2" (AQ)

If I use AE3 as an example: Thursday the techo worked 11 hours so that's 8 Basic Hours, 2 * Time and Half and 1 * Double Time

I basically just want to enter the hours and at the end of the 14 day period have a count of what a technician has worked in terms of Basic and Overtime hours (split by Time and half or Double time)

I'm happy to go with helper columns or whichever is easiest. One thing, though, is that the cells we enter hours in might also have Sick or Hols in them to indicate etc. Therefore any formula needs to manage there being potential text in the range

Attached is a screen shot of the offending spreadsheet

Please, please can you help??

B

Hi

Are you not getting any joy here!
http://www.excelforum.com/excel-form...-possible.html

Might be a good idea to post links if you are cross posting.
If you are going to post a picture, make sure people can see it.

Hi

New to this game and hit a brick wall when it seemed I was so close. So spreading myself. I'm not across the etiquette so if that's the done thing I will know for next time

Still not any closer to a solution, though...

Problem Resolved!

http://www.excelforum.com/excel-form...-possible.html