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
Please, please can you help? By the way - are you not able to include attachments?
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
Please, please can you help? By the way - are you not able to include attachments?
