Bendroji formulė
=(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)
Santrauka
Norėdami apskaičiuoti bendrą darbo valandų skaičių nuo dviejų datų iki laiko, galite naudoti formulę, pagrįstą funkcija NETWORKDAYS. Parodytame pavyzdyje E5 yra ši formulė:
=(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 „apatinis“ yra pavadintas diapazonas H5, o „viršutinis“ - pavadintas diapazonas H6.
Pastaba: šį pavyzdį įkvėpė formulės iššūkis „Chandoo“ ir išsamesnis sprendimas, kurį „MrExcel“ forume pateikė formulės meistras Barry Houdini.
Paaiškinimas
Pagal šią formulę apskaičiuojamas bendras darbo valandų skaičius tarp dviejų datų ir laikų, atsirandančių tarp „apatinio“ ir „viršutinio“ laiko. Parodytame pavyzdyje žemiausias laikas yra 9:00 AM, o viršutinis laikas - 17:00. Jie formulėje rodomi kaip pavadinimai „apatiniai“ ir „viršutiniai“.
Formulės logika yra apskaičiuoti visas įmanomas darbo valandas nuo pradžios iki pabaigos datos imtinai, tada atsargines laiko valandas nuo pradžios datos, esančių nuo pradžios iki žemesnio laiko, ir visas valandas nuo pabaigos datos, atsirandančių tarp pabaigos ir viršutinio laiko.
Funkcija NETWORKDAYS tvarko savaitgalių ir švenčių neįtraukimą (jei ji pateikiama kaip dienų seka). Galite pereiti prie NETWORKDAYS.INTL, jei jūsų tvarkaraštyje yra nestandartinių darbo dienų.
Išvesties formatavimas
Rezultatas yra skaičius, nurodantis visas valandas. Kaip ir visus „Excel“ kartus, jums reikės formatuoti išvestį tinkamu skaičių formatu. Parodytame pavyzdyje mes naudojame:
(h):mm
Kvadratiniai skliaustai sustabdo „Excel“ vartymąsi, kai valandos yra didesnės nei 24. Kitaip tariant, jie leidžia rodyti valandas, viršijančias 24. Jei jums reikia dešimtainės reikšmės valandoms, rezultatą galite padauginti iš 24 ir formatuoti kaip įprastas numeris.
Paprasta versija
Jei pradžios ir pabaigos laikas visada bus tarp apatinio ir viršutinio kartų, galite naudoti paprastesnę šios formulės versiją:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Nėra pradžios ir pabaigos laiko
Jei norite apskaičiuoti bendrą darbo laiką tarp dviejų datų, darant prielaidą, kad visos dienos yra visos darbo dienos, galite naudoti dar paprastesnę formulę:
=NETWORKDAYS(start,end,holidays)*hours
Išsamesnės informacijos žr. Paaiškinimą čia.
