„Excel“

Gaukite darbo valandas tarp datų ir laikų

Get Work Hours Between Dates

„Excel“ formulė: gaukite darbo valandas tarp datų ir laikųBendroji formulė | _+_ | Santrauka

Norėdami apskaičiuoti bendrą darbo laiką tarp dviejų datų ir laikų, galite naudoti formulę, pagrįstą NETWORKDAYS funkcija. Pateiktame pavyzdyje E5 yra ši 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)

kur „žemesnis“ yra pavadintas diapazonas H5 ir „viršutinis“ yra pavadintas diapazonas H6.

kaip rūšiuoti reikšmes suvestinėje lentelėje

Pastaba: šis pavyzdys buvo įkvėptas formulės iššūkis „Chandoo“ , ir išsamesnis sprendimas, pateiktas Formulių meistras Barry Houdini „MrExcel“ forume.





Paaiškinimas

Ši formulė apskaičiuoja bendrą darbo laiką tarp dviejų datų ir laikų, kurie yra tarp „žemesnio“ ir „viršutinio“ laiko. Pateiktame pavyzdyje žemiausias laikas yra 9.00 val., O viršutinis - 17.00 val. Jie rodomi formulėje kaip pavadinti diapazonai „apatinis“ ir „viršutinis“.

Formulės logika yra apskaičiuoti visas įmanomas darbo valandas tarp pradžios ir pabaigos datų, įskaitant, tada atšaukti visas pradžios datos valandas, kurios yra tarp pradžios ir žemesnio laiko, ir visas valandas pabaigos dieną, kurios atsiranda tarp pabaigos laikas ir viršutinis laikas.



The NETWORKDAYS funkcija tvarko savaitgalių ir švenčių dienų neįtraukimą (kai pateikiama kaip datų intervalas). Galite pereiti prie NETWORKDAYS.INTL jei jūsų tvarkaraštyje yra nestandartinių darbo dienų.

Formatavimo išvestis

Rezultatas yra skaičius, reiškiantis bendrą valandų skaičių. Kaip visi „Excel“ kartų , turėsite suformatuoti išvestį tinkamu skaičiaus formatas . Pateiktame pavyzdyje mes naudojame:

 
=( 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)

Kvadratiniai skliausteliai neleidžia „Excel“ apsiversti, kai valandos yra ilgesnės nei 24. Kitaip tariant, jie leidžia rodyti daugiau nei 24. Valandos. Jei jums reikia dešimtainės vertės valandoms, galite padauginkite rezultatą iš 24 ir formatuoti kaip įprastą skaičių.

Paprasta versija

Jei bus pradžios ir pabaigos laikas visada tarp apatinio ir viršutinio laiko, galite naudoti paprastesnę šios formulės versiją:

kurios dvi funkcijos tikrina, ar langeliuose nėra skaitinių simbolių
 
[h]:mm

Nėra pradžios ir pabaigos laiko

Norėdami apskaičiuoti bendrą darbo laiką tarp dviejų datų, darant prielaidą, kad visos dienos yra visos darbo dienos, galite naudoti dar paprastesnę formulę:

 
=( NETWORKDAYS (B5,C5)-1)*(upper-lower)+ MOD (C5,1)- MOD (B5,1)

Pamatyti paaiškinimas čia dėl detalių.

Autorius Dave'as Brunsas


^