Home > Enterprise >  How to count networkdays in a subset of a date range with restrictions?
How to count networkdays in a subset of a date range with restrictions?

Time:02-13

Demo workbook is here, now with formulas: enter image description here

networkdays per each month:

=INDEX(IFNA(VLOOKUP(ROW(A1:A11)&"", QUERY({TEXT(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1) 1)))&"'"), "×"), {"#", "yyy-mm"}), NETWORKDAYS(
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1) 1)))&"'"), "×"),,2), 
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(D2:D11)&"×"&IF(D2:D11="", TODAY(), D2:D11), ".*"&VALUE(UNIQUE(FLATTEN(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), )))))&"'"), "×"),,2))}, 
 "select Col1,sum(Col3) group by Col1 pivot Col2 label Col1'1'"), SEQUENCE(1, COUNTUNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), ))) 1, 0)))

enter image description here

and your monthly metrics would be:

=INDEX(REGEXREPLACE(QUERY(QUERY(SPLIT(FLATTEN(TEXT(TRANSPOSE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), ))), "yyymm\¤mmm-yyy")&"×"&
 REGEXREPLACE(REGEXREPLACE(QUERY(QUERY({TEXT(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1) 1)))&"'"), "×"), {"#", "yyy-mm"}), NETWORKDAYS(
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(C2:C11)&"×"&C2:C11, ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), -1) 1)))&"'"), "×"),,2), 
 INDEX(SPLIT(QUERY(FLATTEN(IF(DAYS(IF(D2:D11="",TODAY(), D2:D11), C2:C11)>=
 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), ROW(C2:C11)&"×"&C2:C11 SEQUENCE(1, MAX(DAYS(IF(D2:D11="", TODAY(), D2:D11), C2:C11) 1), ), NA())), 
 "where Col1 matches '"&TEXTJOIN("|", 1, ROW(D2:D11)&"×"&IF(D2:D11="", TODAY(), D2:D11), ".*"&VALUE(UNIQUE(EOMONTH(SEQUENCE(TODAY()-MIN(C2:C11), 1, MIN(C2:C11)), ))))&"'"), "×"),,2))}, 
 "select sum(Col3) group by Col1 pivot Col2"), "offset 1", 0)&"", "(\b([0-9]|10)\b)", "$1×# Jobs open <= 10 business days")&"", "(.*\d$)", "$1×# Jobs open > 10 business days")), "×"), 
 "where Col2 is not null"), "select Col3,count(Col2) group by Col3 pivot Col1")&"", "(.*¤)", ))

enter image description here

  • Related