Generička formula
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Sažetak
Da biste izračunali radno vrijeme između dva datuma s prilagođenim rasporedom, možete upotrijebiti formulu koja se temelji na funkcijama TJEDAN i SUMPRODUCT, uz pomoć REDA, INDIREKTA i SREDINE. U prikazanom primjeru formula u F8 je:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Koji vraća 36 sati, prema prilagođenom rasporedu gdje se radi 8 sati od ponedjeljka do petka, 4 sata rade u subotu, a ponedjeljak 3. rujna je praznik. Praznici se nude kao imenovani raspon G6: G8. Raspored rada unosi se kao tekstualni niz u stupac D i može se mijenjati po želji.
Napomena: Ovo je formula niza koja se mora unijeti pomoću Control + Shift + Enter. Ako imate standardni radni dan od 8 sati, ova je formula jednostavnija.
Obrazloženje
U osnovi, ova formula koristi funkciju WEEKDAY za utvrđivanje dana u tjednu (tj. Ponedjeljka, utorka itd.) Za svaki dan između dva dana. WEEKDAY vraća broj između 1 i 7. Uz zadane postavke, nedjelja = 1 i subota = 7.
Trik ove formule je sastavljanje niza datuma koje možete unijeti u funkciju TJEDAN. To se radi s ROW s INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretira spojene datume kao brojeve redaka i vraća niz poput ovog:
(43346;43347;43348;43349;43350;43351;43352)
Svaki broj u nizu predstavlja datum. Funkcija WEEKDAY zatim procjenjuje niz i vraća niz vrijednosti u radnim danima:
(2;3;4;5;6;7;1)
Ti brojevi odgovaraju danu u tjednu svakog datuma. Dostavljaju se funkciji MID kao argument početnog broja, zajedno s vrijednošću u D6, "0888884" za tekst:
MID("0888884",(2;3;4;5;6;7;1),1)
Budući da MID-u dajemo niz početnih brojeva, on vraća niz rezultata poput ovog:
("8";"8";"8";"8";"8";"4";"0")
Te vrijednosti odgovaraju radnim satima svakog dana od datuma početka do datuma završetka. Imajte na umu da su vrijednosti u ovom nizu tekst, a ne brojevi. Da bismo pretvorili u stvarne brojeve, množimo s drugim nizom stvorenim za upravljanje praznicima, kako je objašnjeno u nastavku. Matematička operacija prisiljava tekst na numeričke vrijednosti.
Praznici
Za rukovanje praznicima koristimo ISNA, MATCH i imenovani raspon "praznici" ovako:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ovaj izraz koristi MATCH za pronalaženje datuma koji su u praznicima imenovanog raspona koristeći isti niz datuma generiranih gore pomoću INDIRECT i ROW. MATCH vraća broj kada se pronađu praznici i pogrešku # N / A ako nije. Funkcija ISNA "preokreće" rezultate tako da TRUE predstavlja praznike, a FALSE ne praznike. ISNA vraća niz ili rezultate poput ovog:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Konačno, oba polja množe se međusobno unutar SUMPRODUCT-a. Matematička operacija prisiljava TRUE i FALSE na 1 i nulu, a tekstualne vrijednosti u prvom polju na numeričke vrijednosti (kao što je gore objašnjeno), tako da na kraju imamo:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Nakon množenja, unutar SUMPRODUCT imamo jedan niz koji sadrži sva radna vremena u datumskom rasponu:
=SUMPRODUCT((0;8;8;8;8;4;0))
Zatim SUMPRODUCT zbraja sve stavke u polju i vraća rezultat 36.
