Untitled
unknown
plain_text
a year ago
8.0 kB
41
Indexable
=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)
))