Formula generică
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
rezumat
Pentru a număra zilele săptămânii (luni, vineri, duminică etc.) între două date, puteți utiliza o formulă matrice care utilizează mai multe funcții: SUMPRODUCT, WEEKDAY, ROW și INDIRECT. În exemplul prezentat, formula din celula E6 este
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
În versiunea generică a formulei, începutul = data de începere, sfârșitul = data de încheiere și dow = ziua săptămânii.
Explicaţie
La bază, această formulă folosește funcția WEEKDAY pentru a testa o serie de date pentru a vedea dacă aterizează într-o anumită zi a săptămânii (dow) și funcția SUMPRODUCT pentru a calcula totalul.
Când este dată o dată, WEEKDAY returnează pur și simplu un număr între 1 și 7 care corespunde unei anumite zile a săptămânii. Cu setările implicite, 1 = duminică și 7 = sâmbătă. Deci, 2 = luni, 6 = vineri și așa mai departe.
Trucul pentru această formulă este înțelegerea faptului că datele din Excel sunt doar numere de serie care încep la 1 ianuarie 1900. De exemplu, 1 ianuarie 2016 este numărul de serie 42370, iar 8 ianuarie este 42377. Datele din Excel arată doar ca date când se aplică un format de număr de dată.
Deci, întrebarea devine - cum poți construi o serie de date pe care le poți introduce în funcția WEEKDAY pentru a afla zilele corespunzătoare din săptămână?
Răspunsul este să folosiți ROW cu funcții INDIRECTE așa:
ROW(INDIRECT(date1&":"&date2))
INDIRECT permite ca datele concatenate "42370: 42377" să fie interpretate ca numere de rând. Apoi, funcția ROW returnează o matrice ca aceasta:
(42370;42371;42372;42373;42374;42375;42376;42377)
Funcția WEEKDAY evaluează aceste numere ca date și returnează această matrice:
(6;7;1;2;3;4;5;6)
care este testat în funcție de ziua dată a săptămânii (6 în acest caz, de la D6). Odată ce rezultatele testului sunt convertite în 1s și 0s cu cratima dublă, această matrice este procesată de SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Care returnează 2.
Cu SECVENȚĂ
Cu noua funcție SEQUENCE, această formulă se poate simplifica oarecum astfel:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
În această versiune, folosim SEQUENCE pentru a genera direct matricea de date, fără a fi nevoie de INDIRECT sau ROW.
