Generisk formel
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Sammendrag
For å beregne arbeidstid mellom to datoer med en tilpasset tidsplan, kan du bruke en formel basert på WEEKDAY og SUMPRODUCT-funksjonene, med hjelp fra ROW, INDIRECT og MID. I eksemplet vist er formelen i F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Som returnerer 36 timer, basert på en tilpasset tidsplan hvor 8 timer jobbes man-fre, 4 timer jobbes lørdag og mandag 3. september er en ferie. Ferier leveres som det valgte serien G6: G8. Arbeidsplanen legges inn som tekststreng i kolonne D og kan endres etter ønske.
Merk: Dette er en matriseformel som må angis med Control + Shift + Enter. Hvis du har en standard 8-timers arbeidsdag, er denne formelen enklere.
Forklaring
I kjernen bruker denne formelen WEEKDAY-funksjonen til å finne ut ukedagen (dvs. mandag, tirsdag osv.) For hver dag mellom de to gitte datoene. WEEKDAY returnerer et tall mellom 1 og 7. Med standardinnstillinger er søndag = 1 og lørdag = 7.
Trikset med denne formelen er å samle en rekke datoer som du kan mate inn i WEEKDAY-funksjonen. Dette gjøres med ROW med INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW tolker sammenkoblede datoer som radnummer og returnerer en matrise som dette:
(43346;43347;43348;43349;43350;43351;43352)
Hvert tall i matrisen representerer en dato. WEEKDAY-funksjonen evaluerer deretter matrisen og returnerer en matrise med ukedagsverdier:
(2;3;4;5;6;7;1)
Disse tallene tilsvarer ukedagen til hver dato. De blir gitt til MID-funksjonen som startnummerargument, sammen med verdien i D6, "0888884" for tekst:
MID("0888884",(2;3;4;5;6;7;1),1)
Fordi vi gir MID en rekke startnumre, returnerer den en rekke resultater som dette:
("8";"8";"8";"8";"8";"4";"0")
Disse verdiene tilsvarer arbeidstiden hver dag fra startdato til sluttdato. Vær oppmerksom på at verdiene i denne matrisen er tekst, ikke tall. For å konvertere til faktiske tall multipliserer vi med en andre matrise opprettet for å administrere helligdager, som forklart nedenfor. Matematikkoperasjonen tvinger teksten til numeriske verdier.
Ferier
For å håndtere helligdager bruker vi ISNA, MATCH og det navngitte området "helligdager" slik:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Dette uttrykket bruker MATCH for å finne datoer som er i de nevnte rekkevidde helligdager ved hjelp av samme utvalg av datoer generert ovenfor med INDIRECT og ROW. MATCH returnerer et tall når høytider blir funnet, og feilen # N / A når ikke. ISNA-funksjonen "blar" resultatene slik at SANT representerer helligdager og FALSE representerer ikke-helligdager. ISNA returnerer en matrise eller resultater som dette:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Til slutt multipliseres begge matriser med hverandre inne i SUMPRODUCT. Matematikkoperasjonen tvinger SANT og FALSK til 1 og null, og tekstverdiene i den første matrisen til numeriske verdier (som forklart ovenfor), så til slutt har vi:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Etter multiplikasjon har vi en enkelt matrise inne i SUMPRODUCT som inneholder alle arbeidstider i datoperioden:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT summerer deretter alle elementene i matrisen og returnerer et resultat på 36.
