Generieke formule
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Samenvatting
Om werkuren tussen twee datums te berekenen met een aangepast schema, kunt u een formule gebruiken die is gebaseerd op de WEEKDAY- en SUMPRODUCT-functies, met hulp van ROW, INDIRECT en MID. In het getoonde voorbeeld is de formule in F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Dat levert 36 uur op, op basis van een aangepast schema waarbij 8 uur wordt gewerkt van ma-vr, 4 uur wordt gewerkt op zaterdag en maandag 3 september is een feestdag. Feestdagen worden geleverd als de genoemde reeks G6: G8. Het werkrooster wordt als tekstreeks ingevoerd in kolom D en kan naar wens worden gewijzigd.
Opmerking: dit is een matrixformule die moet worden ingevoerd met Control + Shift + Enter. Als u een standaard 8-urige werkdag heeft, is deze formule eenvoudiger.
Uitleg
In de kern gebruikt deze formule de functie WEEKDAY om de dag van de week (dwz maandag, dinsdag, enz.) Te bepalen voor elke dag tussen de twee opgegeven datums. WEEKDAY retourneert een getal tussen 1 en 7. Met standaardinstellingen: zondag = 1 en zaterdag = 7.
De truc van deze formule is het samenstellen van een reeks datums die u in de WEEKDAY-functie kunt invoeren. Dit wordt gedaan met RIJ met INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpreteert de aaneengeschakelde datums als rijnummers en retourneert een array als volgt:
(43346;43347;43348;43349;43350;43351;43352)
Elk getal in de matrix vertegenwoordigt een datum. De WEEKDAY-functie evalueert vervolgens de array en retourneert een array met weekdagwaarden:
(2;3;4;5;6;7;1)
Deze nummers komen overeen met de dag van de week van elke datum. Ze worden aan de MID-functie verstrekt als het startnummerargument, samen met de waarde in D6, "0888884" voor tekst:
MID("0888884",(2;3;4;5;6;7;1),1)
Omdat we MID een reeks startnummers geven, retourneert het een reeks resultaten als volgt:
("8";"8";"8";"8";"8";"4";"0")
Deze waarden komen overeen met de gewerkte uren op elke dag vanaf de begindatum tot de einddatum. Merk op dat de waarden in deze array tekst zijn, geen getallen. Om te converteren naar werkelijke getallen, vermenigvuldigen we met een tweede array die is gemaakt om vakantiedagen te beheren, zoals hieronder wordt uitgelegd. De wiskundige bewerking dwingt de tekst tot numerieke waarden.
Vakantie
Om feestdagen af te handelen, gebruiken we ISNA, MATCH en het genoemde bereik "feestdagen" als volgt:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Deze uitdrukking gebruikt VERGELIJKEN om datums te zoeken die in het benoemde bereik van feestdagen vallen met dezelfde reeks datums die hierboven zijn gegenereerd met INDIRECT en ROW. VERGELIJKEN retourneert een getal wanneer vakanties worden gevonden en de fout # N / B wanneer dat niet het geval is. De ISNA-functie "spiegelt" de resultaten zodat TRUE vakantiedagen vertegenwoordigt en FALSE niet-feestdagen. ISNA retourneert een array of resultaten als volgt:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Ten slotte worden beide arrays binnen SUMPRODUCT met elkaar vermenigvuldigd. De wiskundige bewerking dwingt TRUE en FALSE naar 1 en nul, en de tekstwaarden in de eerste array naar numerieke waarden (zoals hierboven uitgelegd), dus uiteindelijk hebben we:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Na vermenigvuldiging hebben we een enkele array in SOMPRODUCT met alle werkuren in het datumbereik:
=SUMPRODUCT((0;8;8;8;8;4;0))
SOMPRODUCT telt vervolgens alle items in de array op en retourneert een resultaat van 36.
