Üldine valem
=(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)
Kokkuvõte
Kahe kuupäeva ja kellaaja vahelise töötundide arvutamiseks võite kasutada funktsiooni NETWORKDAYS põhinevat valemit. Näidatud näites sisaldab E5 järgmist valemit:
=(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)
kus "alumine" on nimega vahemik H5 ja "ülemine" on nimega vahemik H6.
Pange tähele: see näide sai inspiratsiooni Chandoo vormelite väljakutsest ja terviklikumast lahendusest, mille pakkus vormelimeister Barry Houdini MrExceli foorumis.
Selgitus
Selle valemi abil arvutatakse kogu tööaeg kahe kuupäeva ja kellaaja vahel, mis toimub "alumise" ja "ülemise" aja vahel. Näidatud näites on alumine aeg 9:00 ja ülemine kell 17:00. Need ilmuvad valemis nimega vahemikud "alumine" ja "ülemine".
Valemi loogika on arvutada kõik võimalikud töötunnid algus- ja lõppkuupäeva vahel (kaasa arvatud), seejärel varundada alguskuupäeval kõik tunnid, mis tekivad algus- ja madalama aja vahel, ning kõik lõpu kuupäeva tunnid, mis tekivad vahemikus lõpuaeg ja ülemine aeg.
Funktsioon NETWORKDAYS tegeleb nädalavahetuste ja pühade väljajätmisega (kui see on esitatud kuupäevavahemikuna). Võite minna NETWORKDAYS.INTL-le, kui teie ajakavas on mittestandardsed tööpäevad.
Väljundi vormindamine
Tulemuseks on arv, mis tähistab kogu tundi. Nagu kõik Exceli ajad, peate ka vormindama väljundi sobiva numbrivorminguga. Näidatud näites kasutame järgmist:
(h):mm
Nurksulgudes peatatakse Exceli ümberpaiknemine, kui tunnid on suuremad kui 24. Teisisõnu võimaldavad need kuvada tunde, mis on suuremad kui 24. Kui vajate tundide kohta kümnendarvu, võite tulemuse korrutada 24-ga ja vormindada kui tavaline number.
Lihtne versioon
Kui algus- ja lõpuajad toimuvad alati alumise ja ülemise aja vahel, võite kasutada selle valemi lihtsamat versiooni:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Algus- ja lõppaega pole
Kahe kuupäeva vahelise kogu töötundide arvutamiseks võite kasutada veelgi lihtsamat valemit, eeldades, et kõik päevad on täispäevad
=NETWORKDAYS(start,end,holidays)*hours
Lisateavet leiate siit.
