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