I have converted daily working into binary (C2:C43), this is then relative to ePayfact Code (B2:B43)
Col E is an actual work pattern which needs an ePayfact Code allocating to it. If col E starts with "37","37Hrs","37 Hrs" then col G = FT (B42)
However Row 21 = "32.64Hrs" and it brings in FT.
Formulas used are as follows -
F2 - =TEXTJOIN("",,ISNUMBER(SEARCH("(* "&{"Sn","M","T","W","Th","F","S"}&"=* *)",SUBSTITUTE(SUBSTITUTE(E2,"(","( "),")"," )")))+0)
G2 - =IF(ISNUMBER(SEARCH("37*",E2)),$B$42,INDEX($B$2:$B$43,MATCH(F2,$C$2:$C$43,0)))
There are a few examples of this throughout the table - is it something to do with it being a circular reference? I'm completely at a loss as to how to resolve this now!
Also Row 37 - it is a 2 week working pattern, 4 days each week. But the result is 501, suggesting they work 5 days - as the days differ from wk1 to wk2 - is there any way around this?
Col E is an actual work pattern which needs an ePayfact Code allocating to it. If col E starts with "37","37Hrs","37 Hrs" then col G = FT (B42)
However Row 21 = "32.64Hrs" and it brings in FT.
Formulas used are as follows -
F2 - =TEXTJOIN("",,ISNUMBER(SEARCH("(* "&{"Sn","M","T","W","Th","F","S"}&"=* *)",SUBSTITUTE(SUBSTITUTE(E2,"(","( "),")"," )")))+0)
G2 - =IF(ISNUMBER(SEARCH("37*",E2)),$B$42,INDEX($B$2:$B$43,MATCH(F2,$C$2:$C$43,0)))
There are a few examples of this throughout the table - is it something to do with it being a circular reference? I'm completely at a loss as to how to resolve this now!
Also Row 37 - it is a 2 week working pattern, 4 days each week. But the result is 501, suggesting they work 5 days - as the days differ from wk1 to wk2 - is there any way around this?