Shift Time Between Calculation Google Sheet-CodePudding

I am trying to calculate the salary for employees in Google Sheets.

Night Shift - if shift start time lies between 12 am-6am
Day Shift - if shift start time lies between 6am-11:59pm

I am able to calculate the salary for night shift employee(D7), I dont know to what am I doing wrong, but I cant seem to add more conditions like day shift and if employee present.

my requirement is:

if employee present & Night shift then 500  
if employee present & day shift then 400

=ARRAYFORMULA(IFERROR(IF((TIMEVALUE(A8)>=TIMEVALUE("4:00:00"))(TIMEVALUE(B8)< TIMEVALUE("10:00:00")), 250,IF((TIMEVALUE(A8)>=TIMEVALUE("06:00:00")) (TIMEVALUE(B8)<=TIMEVALUE("23:59:59")), 225 && IF(REGEXMATCH(C8, "P|p"), 225, 0))))

any help is appreciate. Here is the

CodePudding user response：

try:

=INDEX(IF(C7:C="Present", IFNA(VLOOKUP(A7:A, 
 {0, 500; "6:00"*1, 400; "12:00"*1, 500}, 2, 1)), ))