These are the formulae I am currently using where I have to manually sort out if its friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula. I have tried using “COUNTIFS” & “IF” but am unable to use it correctly.
No. of hrs worked = Time Out – Time in – 2hrs of break
Formula used; [=Text(C2-B2-TIME(2,0,0),”h:mm”)]
Formula used to change no. of hrs worked to an integer where 30 minutes = 0.5 ; [=INT(C2-B2-TIME(2,0,0))*24+HOUR(C2-B2-TIME(2,0,0))+ROUND(MINUTE(C2-B2-TIME(2,0,0))/60,2)]
From Monday to Thursday;
Before 6pm, the column F will be the no. of hrs * $ 10. Formula used; [=(INT(E2-(C2-TIME(18,0,0)))*24+HOUR(E2-(C2-TIME(18,0,0)))+ROUND(MINUTE(E2-(C2-TIME(18,0,0)))/60,2))*10].
After 6pm, the column G will be the no. of hrs till time out *$ 11. Formula used; [=(INT(C2-TIME(18,0,0))*24+HOUR(C2-TIME(18,0,0))+ROUND(MINUTE(C2-TIME(18,0,0))/60,2))*11].
On Friday;
Before 6pm, the column F will be the no. of hrs * $ 10. Formula used; [=(INT(E4-(C4-TIME(18,0,0)))*24+HOUR(E4-(C4-TIME(18,0,0)))+ROUND(MINUTE(E4-(C4-TIME(18,0,0)))/60,2))*10].
After 6pm the column H will be the no. of hrs till time out *$ 12. Formula used; [=(INT(C4-TIME(18,0,0))*24+HOUR(C4-TIME(18,0,0))+ROUND(MINUTE(C4-TIME(18,0,0))/60,2))*12].
Weekends;
No. of hrs * $ 12. a [=D5*12].
