0

enter image description here

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

=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 6 pm, the column F will be the no. of hrs * $10

=(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 6 pm, the column G will be the no. of hrs till time out *$11.

=(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 6 pm, the column F will be the no. of hrs * $10.

=(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 6 pm the column H will be the no. of hrs till time out *$12:

=(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.

=D5*12
0

0

cell D2:

=ARRAYFORMULA(IF(LEN(B2:B), TEXT(C2:C-B2:B-2/24, "h:mm")*24, ))

cell E2:

=ARRAYFORMULA(IF(LEN(B2:B), TEXT(C2:C-B2:B-2/24, "h:mm"), ))

cell F2:

=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(WEEKDAY(A2:A,2)), "[1-5]"), 
   (INT(E2:E-(C2:C-TIME(18, 0, 0)))*24+
   HOUR(E2:E-(C2:C-TIME(18, 0, 0)))+ROUND(
 MINUTE(E2:E-(C2:C-TIME(18, 0, 0)))/60, 2))*F1, ))

cell G2:

=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(WEEKDAY(A2:A, 2)), "[1-4]"), 
   (INT(C2:C-TIME(18, 0, 0))*24+
   HOUR(C2:C-TIME(18, 0, 0))+ROUND(
 MINUTE(C2:C-TIME(18, 0, 0))/60, 2))*G1, ))

cell H2:

=ARRAYFORMULA(IF(LEN(C2:C), IF(REGEXMATCH(TO_TEXT(WEEKDAY(A2:A, 2)), "[5]"), 
   (INT(C2:C-TIME(18,0,0))*24+
   HOUR(C2:C-TIME(18,0,0))+ROUND(
 MINUTE(C2:C-TIME(18,0,0))/60,2)) * H1, 
 IF(REGEXMATCH(TO_TEXT(WEEKDAY(A2:A, 2)), "[6-7]"), D2:D*H1, )), ))

cell I2:

=ARRAYFORMULA(IF(LEN(C2:C), F2:F+G2:G+H2:H, ))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.