Generisk formel
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Resumé
For at beregne arbejdstid mellem to datoer med en tilpasset tidsplan kan du bruge en formel baseret på WEEKDAY og SUMPRODUCT-funktionerne med hjælp fra ROW, INDIRECT og MID. I det viste eksempel er formlen i F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Hvilket returnerer 36 timer, baseret på en brugerdefineret tidsplan, hvor 8 timer arbejdes mand-fre, 4 timer arbejdes lørdag og mandag 3. september er en ferie. Ferier leveres som det navngivne område G6: G8. Arbejdsplanen indtastes som en tekststreng i kolonne D og kan ændres efter ønske.
Bemærk: Dette er en matrixformel, der skal indtastes med Control + Shift + Enter. Hvis du har en standard 8 timers hverdag, er denne formel enklere.
Forklaring
I kernen bruger denne formel WEEKDAY-funktionen til at finde ud af ugedagen (dvs. mandag, tirsdag osv.) For hver dag mellem de to givne datoer. WEEKDAY returnerer et tal mellem 1 og 7. Med standardindstillinger er søndag = 1 og lørdag = 7.
Tricket til denne formel samler en række datoer, som du kan føje til WEEKDAY-funktionen. Dette gøres med ROW med INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW fortolker de sammenkædede datoer som rækkenumre og returnerer en matrix som denne:
(43346;43347;43348;43349;43350;43351;43352)
Hvert nummer i arrayet repræsenterer en dato. WEEKDAY-funktionen evaluerer derefter arrayet og returnerer et array med hverdagsværdier:
(2;3;4;5;6;7;1)
Disse tal svarer til ugedagen for hver dato. De leveres til MID-funktionen som startnummerargument sammen med værdien i D6, "0888884" til tekst:
MID("0888884",(2;3;4;5;6;7;1),1)
Fordi vi giver MID en række startnumre, returnerer den en række resultater som denne:
("8";"8";"8";"8";"8";"4";"0")
Disse værdier svarer til de arbejdede timer hver dag fra startdato til slutdato. Bemærk, at værdierne i denne matrix er tekst, ikke tal. For at konvertere til faktiske tal multipliceres vi med et andet array oprettet til at styre helligdage, som forklaret nedenfor. Matematikoperationen tvinger teksten til numeriske værdier.
Helligdage
For at håndtere helligdage bruger vi ISNA, MATCH og det navngivne interval "helligdage" som denne:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Dette udtryk bruger MATCH til at finde datoer, der er i de navngivne helligdage, ved hjælp af det samme array af datoer genereret ovenfor med INDIRECT og ROW. MATCH returnerer et tal, når helligdage findes, og fejlen # N / A når ikke. ISNA-funktionen "vender" resultaterne, så SAND repræsenterer helligdage og FALSK repræsenterer ikke-helligdage. ISNA returnerer en matrix eller resultater som denne:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Endelig ganges begge arrays med hinanden inde i SUMPRODUCT. Matematikoperationen tvinger SAND og FALSK til 1 og nul, og tekstværdierne i det første array til numeriske værdier (som forklaret ovenfor), så i sidste ende har vi:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Efter multiplikation har vi et enkelt array inde i SUMPRODUCT, der indeholder alle arbejdstimer i datointervallet:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT summerer derefter alle elementer i arrayet og returnerer et resultat på 36.
