Google-sheets – What function formula should I use to calculate the salary so that I don’t have to differentiate between Friday and weekend manually

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesregex

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

Best Answer

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, ))

Related Topic