Vispārēja 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)
Kopsavilkums
Lai aprēķinātu kopējo darba stundu skaitu starp diviem datumiem un laikiem, varat izmantot formulu, kuras pamatā ir funkcija NETWORKDAYS. Parādītajā piemērā E5 satur šo 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)
kur "apakšējais" ir nosauktais diapazons H5 un "augšējais" ir nosauktais diapazons H6.
Piezīme: šo piemēru iedvesmoja formulu izaicinājums Chandoo, kā arī pilnīgāks risinājums, ko MrExcel forumā sniedza formulu meistars Berijs Houdini.
Paskaidrojums
Šī formula aprēķina kopējo darba stundu skaitu starp diviem datumiem un laikiem, kas notiek starp “zemāko” un “augšējo” laiku. Parādītajā piemērā zemākais laiks ir 9:00 un augšējais laiks 17:00. Tie parādās formulā kā nosauktie diapazoni "apakšējais" un "augšējais".
Formulas loģika ir aprēķināt visu iespējamo darba laiku starp sākuma un beigu datumiem (ieskaitot), pēc tam dublēt visas sākuma dienas stundas, kas notiek starp sākuma laiku un zemāko laiku, un visas stundas beigu datumā, kas notiek starp beigu laiks un augšējais laiks.
Funkcija NETWORKDAYS rīkojas ar nedēļas nogales un svētku dienu izslēgšanu (ja tā ir norādīta kā datumu diapazons). Ja jūsu grafikam ir nestandarta darba dienas, varat pāriet uz NETWORKDAYS.INTL.
Formatē izvadi
Rezultāts ir skaitlis, kas apzīmē kopējo stundu skaitu. Tāpat kā visos Excel laikos, jums būs jāformatē izeja ar piemērotu skaitļu formātu. Parādītajā piemērā mēs izmantojam:
(h):mm
Kvadrātiekavās tiek pārtraukta Excel pārvietošanās, kad stundas ir lielākas par 24. Citiem vārdiem sakot, tie ļauj parādīt stundas, kas lielākas par 24. Ja jums ir nepieciešama stundu decimālvērtība, rezultātu varat reizināt ar 24 un formatēt kā parastais numurs.
Vienkārša versija
Ja sākuma un beigu laiks vienmēr notiks starp apakšējo un augšējo laiku, varat izmantot šīs formulas vienkāršāku versiju:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Nav sākuma un beigu laika
Lai aprēķinātu kopējo darba stundu skaitu starp diviem datumiem, pieņemot, ka visas dienas ir pilnas darba dienas, varat izmantot vēl vienkāršāku formulu:
=NETWORKDAYS(start,end,holidays)*hours
Sīkāku informāciju skatiet paskaidrojumā šeit.
