Generička formula
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Sažetak
Da biste izračunali ukupni broj radnih sati između dva datuma i vremena, možete koristiti formulu koja se temelji na funkciji NETWORKDAYS. U prikazanom primjeru, E5 sadrži ovu formulu:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
pri čemu je "donji" imenovani raspon H5, a "gornji" imenovani raspon H6.
Napomena: ovaj je primjer nadahnut izazovom formule na Chandoo-u i cjelovitijim rješenjem koje je pružio majstor formule Barry Houdini na forumu MrExcel.
Obrazloženje
Ova formula izračunava ukupno radno vrijeme između dva datuma i vremena, koje se javljaju između "donjeg" i "gornjeg" vremena. U prikazanom primjeru, donje vrijeme je 9:00, a gornje 17:00. Oni se u formuli pojavljuju kao imenovani rasponi "donji" i "gornji".
Logika formule je izračunati sva moguća radna vremena između datuma početka i završetka, zatim vratiti sve sate na datum početka koji se javljaju između vremena početka i nižeg vremena i bilo koje sate na datum završetka koji se javljaju između krajnje vrijeme i gornje vrijeme.
Funkcija NETWORKDAYS obrađuje izuzeće vikenda i praznika (ako je navedena u rasponu datuma). Možete se prebaciti na NETWORKDAYS.INTL ako vaš raspored ima nestandardne radne dane.
Formatiranje izlaza
Rezultat je broj koji predstavlja ukupan broj sati. Kao i sva Excel-ova vremena, morat ćete formatirati izlaz u prikladan format broja. U prikazanom primjeru koristimo:
(h):mm
Uglate zagrade sprečavaju Excel da se prevrće kada su sati veći od 24. Drugim riječima, omogućuju prikaz sati većih od 24. Ako vam je potrebna decimalna vrijednost za sate, rezultat možete pomnožiti s 24 i oblikovati kao redoviti broj.
Jednostavna verzija
Ako se vrijeme početka i završetka uvijek događa između donjeg i gornjeg vremena, možete koristiti jednostavniju verziju ove formule:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Nema vremena početka i završetka
Da biste izračunali ukupan broj radnih sati između dva datuma, pod pretpostavkom da su svi dani puni radni dani, možete upotrijebiti još jednostavniju formulu:
=NETWORKDAYS(start,end,holidays)*hours
Pogledajte objašnjenje ovdje za detalje.
