Generisk formel
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Sammanfattning
För att beräkna arbetstid mellan två datum med ett anpassat schema kan du använda en formel baserad på funktionerna VECKA- och SUMPRODUKT, med hjälp av ROW, INDIRECT och MID. I exemplet som visas är formeln i F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Som returnerar 36 timmar, baserat på ett anpassat schema där 8 timmar arbetas mån-fre, 4 timmar arbetas på lördag och måndag 3 september är en helgdag. Semester levereras som namngivet sortiment G6: G8. Arbetsschemat anges som en textsträng i kolumn D och kan ändras efter önskemål.
Obs: Detta är en matrisformel som måste anges med Control + Shift + Enter. Om du har en standard 8-timmars arbetsdag är den här formeln enklare.
Förklaring
I grunden använder denna formel WEEKDAY-funktionen för att räkna ut veckodagen (dvs. måndag, tisdag etc.) för varje dag mellan de två angivna datumen. WEEKDAY returnerar ett tal mellan 1 och 7. Med standardinställningar, söndag = 1 och lördag = 7.
Tricket med denna formel är att sätta ihop en rad datum som du kan mata in i WEEKDAY-funktionen. Detta görs med ROW med INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW tolkar sammanfogade datum som radnummer och returnerar en matris så här:
(43346;43347;43348;43349;43350;43351;43352)
Varje nummer i matrisen representerar ett datum. WEEKDAY-funktionen utvärderar sedan arrayen och returnerar en array med veckodagsvärden:
(2;3;4;5;6;7;1)
Dessa siffror motsvarar veckodagen för varje datum. De tillhandahålls MID-funktionen som startnummerargument, tillsammans med värdet i D6, "0888884" för text:
MID("0888884",(2;3;4;5;6;7;1),1)
Eftersom vi ger MID en rad startnummer returnerar den en rad resultat så här:
("8";"8";"8";"8";"8";"4";"0")
Dessa värden motsvarar de arbetade timmarna varje dag från startdatum till slutdatum. Observera att värdena i denna matris är text, inte siffror. För att konvertera till faktiska tal multiplicerar vi med en andra matris som skapats för att hantera helgdagar, som förklaras nedan. Matematikoperationen tvingar texten till numeriska värden.
Högtider
För att hantera helgdagar använder vi ISNA, MATCH och det namngivna intervallet "helgdagar" så här:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Detta uttryck använder MATCH för att lokalisera datum som ligger i namngivna intervallhelger med samma matris av datum som genereras ovan med INDIRECT och ROW. MATCH returnerar ett nummer när helgdagar hittas och # N / A-felet när inte. ISNA-funktionen "vänder" resultaten så att SANT representerar helgdagar och FALSKT representerar icke-helgdagar. ISNA returnerar en matris eller resultat så här:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Slutligen multipliceras båda matriserna med varandra inuti SUMPRODUCT. Matematikoperationen tvingar SANT och FALSKT till 1 och noll, och textvärdena i den första matrisen till numeriska värden (som förklaras ovan), så till slut har vi:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Efter multiplikation har vi en enda matris inne i SUMPRODUCT som innehåller alla arbetstider i datumintervallet:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT summerar sedan alla objekt i matrisen och returnerar resultatet 36.
