Generisk formel
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Sammanfattning
För att räkna vardagar (måndagar, fredagar, söndagar etc.) mellan två datum kan du använda en matrisformel som använder flera funktioner: SUMPRODUKT, VECKODAG, RAD och INDIREKT. I exemplet som visas är formeln i cell E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
I den generiska versionen av formeln, start = startdatum, slut = slutdatum och dow = veckodag.
Förklaring
I grunden använder denna formel WEEKDAY-funktionen för att testa ett antal datum för att se om de landar på en viss veckodag (dow) och SUMPRODUCT-funktionen för att räkna upp summan.
När ett datum ges, returnerar WEEKDAY helt enkelt ett tal mellan 1 och 7 som motsvarar en viss veckodag. Med standardinställningar är 1 = söndag och 7 = lördag. Så, 2 = måndag, 6 = fredag och så vidare.
Tricket med denna formel är att förstå att datum i Excel bara är serienummer som börjar den 1 januari 1900. Till exempel är 1 januari 2016 serienumret 42370 och 8 januari är 42377. Datum i Excel ser bara ut som datum när ett datumnummerformat tillämpas.
Så frågan blir - hur kan du skapa en matris med datum som du kan mata in i WEEKDAY-funktionen för att ta reda på motsvarande veckodagar?
Svaret är att använda ROW med INDIRECT-funktioner så här:
ROW(INDIRECT(date1&":"&date2))
INDIRECT tillåter att de sammanhängande datumen "42370: 42377" tolkas som radnummer. Då returnerar ROW-funktionen en matris så här:
(42370;42371;42372;42373;42374;42375;42376;42377)
WEEKDAY-funktionen utvärderar dessa siffror som datum och returnerar denna array:
(6;7;1;2;3;4;5;6)
som testas mot den angivna veckodagen (6 i detta fall från D6). När testresultaten har konverterats till 1s och 0s med dubbel bindestreck bearbetas denna array av SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Vilket returnerar 2.
Med SEKVENS
Med den nya SEQUENCE-funktionen kan denna formel förenklas ungefär så här:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
I den här versionen använder vi SEQUENCE för att generera datumuppsättningen direkt, utan behov av INDIRECT eller ROW.
