Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
8.0 kB
41
Indexable
Never
=IF(T11=0,
  IF(T12=0,
    IF(T4<>"", IFERROR(VLOOKUP($A4&T4,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T5<>"", IFERROR(VLOOKUP($A5&T5,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T6<>"", IFERROR(VLOOKUP($A6&T6,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T7<>"", IFERROR(VLOOKUP($A7&T7,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T8<>"", IFERROR(VLOOKUP($A8&T8,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T9<>"", IFERROR(VLOOKUP($A9&T9,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T10<>"", IFERROR(VLOOKUP($A10&T10,'Vlookup Data'!$D:$E,2,0),0),0) &
    " & " & COUNTIF(T4:T10,"*HOL*") & " HOL & " & COUNTIF(T4:T10,"*SICK*") & " SICK",
 
    IF(T4<>"", IF(ISNUMBER(FIND(" to ", T4)), 
         IF((TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4)))-TIMEVALUE(MID(T4, 1, FIND(" to ", T4)-1)))*24>5, 
            (TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4))) - TIMEVALUE(MID(T4, 1, FIND(" to ", T4) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4))) - TIMEVALUE(MID(T4, 1, FIND(" to ", T4) - 1))) * 24), 
         IFERROR(VLOOKUP($A4&T4,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T5<>"", IF(ISNUMBER(FIND(" to ", T5)), 
         IF((TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5)))-TIMEVALUE(MID(T5, 1, FIND(" to ", T5)-1)))*24>5, 
            (TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5))) - TIMEVALUE(MID(T5, 1, FIND(" to ", T5) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5))) - TIMEVALUE(MID(T5, 1, FIND(" to ", T5) - 1))) * 24), 
         IFERROR(VLOOKUP($A5&T5,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T6<>"", IF(ISNUMBER(FIND(" to ", T6)), 
         IF((TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6)))-TIMEVALUE(MID(T6, 1, FIND(" to ", T6)-1)))*24>5, 
            (TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6))) - TIMEVALUE(MID(T6, 1, FIND(" to ", T6) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6))) - TIMEVALUE(MID(T6, 1, FIND(" to ", T6) - 1))) * 24), 
         IFERROR(VLOOKUP($A6&T6,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T7<>"", IF(ISNUMBER(FIND(" to ", T7)), 
         IF((TIMEVALUE(MID(T7,FIND(" to ", T7) + 4, LEN(T7)))-TIMEVALUE(MID(T7, 1, FIND(" to ", T7)-1)))*24>5, 
            (TIMEVALUE(MID(T7, FIND(" to ", T7) + 4, LEN(T7))) - TIMEVALUE(MID(T7, 1, FIND(" to ", T7) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T7, FIND(" to ", T7) + 4, LEN(T7))) - TIMEVALUE(MID(T7, 1, FIND(" to ", T7) - 1))) * 24), 
         IFERROR(VLOOKUP($A7&T7,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T8<>"", IF(ISNUMBER(FIND(" to ", T8)), 
         IF((TIMEVALUE(MID(T8,FIND(" to ", T8) + 4, LEN(T8)))-TIMEVALUE(MID(T8, 1, FIND(" to ", T8)-1)))*24>5, 
            (TIMEVALUE(MID(T8, FIND(" to ", T8) + 4, LEN(T8))) - TIMEVALUE(MID(T8, 1, FIND(" to ", T8) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T8, FIND(" to ", T8) + 4, LEN(T8))) - TIMEVALUE(MID(T8, 1, FIND(" to ", T8) - 1))) * 24), 
         IFERROR(VLOOKUP($A8&T8,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T9<>"", IF(ISNUMBER(FIND(" to ", T9)), 
         IF((TIMEVALUE(MID(T9,FIND(" to ", T9) + 4, LEN(T9)))-TIMEVALUE(MID(T9, 1, FIND(" to ", T9)-1)))*24>5, 
            (TIMEVALUE(MID(T9, FIND(" to ", T9) + 4, LEN(T9))) - TIMEVALUE(MID(T9, 1, FIND(" to ", T9) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T9, FIND(" to ", T9) + 4, LEN(T9))) - TIMEVALUE(MID(T9, 1, FIND(" to ", T9) - 1))) * 24), 
         IFERROR(VLOOKUP($A9&T9,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T10<>"", IF(ISNUMBER(FIND(" to ", T10)), 
         IF((TIMEVALUE(MID(T10,FIND(" to ", T10) + 4, LEN(T10)))-TIMEVALUE(MID(T10, 1, FIND(" to ", T10)-1)))*24>5, 
            (TIMEVALUE(MID(T10, FIND(" to ", T10) + 4, LEN(T10))) - TIMEVALUE(MID(T10, 1, FIND(" to ", T10) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T10, FIND(" to ", T10) + 4, LEN(T10))) - TIMEVALUE(MID(T10, 1, FIND(" to ", T10) - 1))) * 24), 
         IFERROR(VLOOKUP($A10&T10,'Vlookup Data'!$D:$E,2,0),0)), 0) &
    " & " & COUNTIF(T4:T10,"*HOL*") & " HOL & " & COUNTIF(T4:T10,"*SICK*") & " SICK")
,
  IF(T12=0, T11 +
    IF(T4<>"", IFERROR(VLOOKUP($A4&T4,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T5<>"", IFERROR(VLOOKUP($A5&T5,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T6<>"", IFERROR(VLOOKUP($A6&T6,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T7<>"", IFERROR(VLOOKUP($A7&T7,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T8<>"", IFERROR(VLOOKUP($A8&T8,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T9<>"", IFERROR(VLOOKUP($A9&T9,'Vlookup Data'!$D:$E,2,0),0),0) +
    IF(T10<>"", IFERROR(VLOOKUP($A10&T10,'Vlookup Data'!$D:$E,2,0),0),0) &
    " & " & COUNTIF(T4:T10,"*HOL*") & " HOL & " & COUNTIF(T4:T10,"*SICK*") & " SICK",
    T11 +  IF(T4<>"", IF(ISNUMBER(FIND(" to ", T4)), 
         IF((TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4)))-TIMEVALUE(MID(T4, 1, FIND(" to ", T4)-1)))*24>5, 
            (TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4))) - TIMEVALUE(MID(T4, 1, FIND(" to ", T4) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T4, FIND(" to ", T4) + 4, LEN(T4))) - TIMEVALUE(MID(T4, 1, FIND(" to ", T4) - 1))) * 24), 
         IFERROR(VLOOKUP($A4&T4,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T5<>"", IF(ISNUMBER(FIND(" to ", T5)), 
         IF((TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5)))-TIMEVALUE(MID(T5, 1, FIND(" to ", T5)-1)))*24>5, 
            (TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5))) - TIMEVALUE(MID(T5, 1, FIND(" to ", T5) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T5, FIND(" to ", T5) + 4, LEN(T5))) - TIMEVALUE(MID(T5, 1, FIND(" to ", T5) - 1))) * 24), 
         IFERROR(VLOOKUP($A5&T5,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T6<>"", IF(ISNUMBER(FIND(" to ", T6)), 
         IF((TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6)))-TIMEVALUE(MID(T6, 1, FIND(" to ", T6)-1)))*24>5, 
            (TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6))) - TIMEVALUE(MID(T6, 1, FIND(" to ", T6) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T6, FIND(" to ", T6) + 4, LEN(T6))) - TIMEVALUE(MID(T6, 1, FIND(" to ", T6) - 1))) * 24), 
         IFERROR(VLOOKUP($A6&T6,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T7<>"", IF(ISNUMBER(FIND(" to ", T7)), 
         IF((TIMEVALUE(MID(T7,FIND(" to ", T7) + 4, LEN(T7)))-TIMEVALUE(MID(T7, 1, FIND(" to ", T7)-1)))*24>5, 
            (TIMEVALUE(MID(T7, FIND(" to ", T7) + 4, LEN(T7))) - TIMEVALUE(MID(T7, 1, FIND(" to ", T7) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T7, FIND(" to ", T7) + 4, LEN(T7))) - TIMEVALUE(MID(T7, 1, FIND(" to ", T7) - 1))) * 24), 
         IFERROR(VLOOKUP($A7&T7,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T8<>"", IF(ISNUMBER(FIND(" to ", T8)), 
         IF((TIMEVALUE(MID(T8,FIND(" to ", T8) + 4, LEN(T8)))-TIMEVALUE(MID(T8, 1, FIND(" to ", T8)-1)))*24>5, 
            (TIMEVALUE(MID(T8, FIND(" to ", T8) + 4, LEN(T8))) - TIMEVALUE(MID(T8, 1, FIND(" to ", T8) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T8, FIND(" to ", T8) + 4, LEN(T8))) - TIMEVALUE(MID(T8, 1, FIND(" to ", T8) - 1))) * 24), 
         IFERROR(VLOOKUP($A8&T8,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T9<>"", IF(ISNUMBER(FIND(" to ", T9)), 
         IF((TIMEVALUE(MID(T9,FIND(" to ", T9) + 4, LEN(T9)))-TIMEVALUE(MID(T9, 1, FIND(" to ", T9)-1)))*24>5, 
            (TIMEVALUE(MID(T9, FIND(" to ", T9) + 4, LEN(T9))) - TIMEVALUE(MID(T9, 1, FIND(" to ", T9) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T9, FIND(" to ", T9) + 4, LEN(T9))) - TIMEVALUE(MID(T9, 1, FIND(" to ", T9) - 1))) * 24), 
         IFERROR(VLOOKUP($A9&T9,'Vlookup Data'!$D:$E,2,0),0)), 0) +
    IF(T10<>"", IF(ISNUMBER(FIND(" to ", T10)), 
         IF((TIMEVALUE(MID(T10,FIND(" to ", T10) + 4, LEN(T10)))-TIMEVALUE(MID(T10, 1, FIND(" to ", T10)-1)))*24>5, 
            (TIMEVALUE(MID(T10, FIND(" to ", T10) + 4, LEN(T10))) - TIMEVALUE(MID(T10, 1, FIND(" to ", T10) - 1))) * 24 - 0.5, 
            (TIMEVALUE(MID(T10, FIND(" to ", T10) + 4, LEN(T10))) - TIMEVALUE(MID(T10, 1, FIND(" to ", T10) - 1))) * 24), 
         IFERROR(VLOOKUP($A10&T10,'Vlookup Data'!$D:$E,2,0),0)), 0)
))