Excel

Pridobite delovne ure med datumi in urami

Get Work Hours Between Dates

Formula Excel: Pridobite delovne ure med datumi in uramiSplošna formula | _+_ | Povzetek

Za izračun skupnih delovnih ur med dvema datumoma in urami lahko uporabite formulo, ki temelji na funkciji NETWORKDAYS. V prikazanem primeru E5 vsebuje to formulo:

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

kjer je 'nižje' imenovani obseg H5 in „zgornji“ je poimenovano območje H6.



kaj je podatkovna serija v excelu

Opomba: ta primer je navdihnil izziv formule za Chandoo , in celovitejšo rešitev, ki jo ponuja mojster formule Barry Houdini na forumu MrExcel.



Pojasnilo

Ta formula izračuna skupne delovne ure med dvema datumoma in urami, ki nastanejo med 'spodnjim' in 'zgornjim' časom. V prikazanem primeru je spodnji čas 9.00, zgornji pa 17.00. Ti so v formuli prikazani kot imenovanih obsegov 'spodnji' in 'zgornji'.

Logika formule je izračunati vse možne delovne ure med začetnim in končnim datumom, vključno s tem, da se nato vrnejo vse ure na začetni datum, ki se pojavijo med začetnim časom in nižjim časom, in vse ure na končni datum, ki se pojavijo med zadnji in zgornji čas.



The Funkcija NETWORKDAYS obravnava izključitev vikendov in praznikov (če je na voljo v različnih datumih). Lahko preklopite na NETWORKDAYS.INTL če ima vaš urnik nestandardne delovne dni.

Oblikovanje izhoda

Rezultat je število, ki predstavlja skupno število ur. Tako kot vsi Excelovi časi , boste morali izhod formatirati z ustreznim številčni obliki . V prikazanem primeru uporabljamo:

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

Pravokotni oklepaji preprečujejo, da bi se Excel prevrnil, ko so ure večje od 24. Z drugimi besedami, omogočajo prikaz ur, večjih od 24. Če potrebujete decimalno vrednost za ure, lahko rezultat pomnožite s 24 in oblikovati kot običajno številko.



Enostavna različica

Če bo čas začetka in konca nenehno pojavijo med spodnjim in zgornjim časom, lahko uporabite enostavnejšo različico te formule:

kako izračunati indeks v
 
[h]:mm

Ni začetnega in končnega časa

Če želite izračunati skupne delovne ure med dvema datumoma, če so vsi dnevi polni delovni dnevi, lahko uporabite še enostavnejšo formulo:

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

Glej razlaga tukaj za podrobnosti.

Avtor Dave Bruns


^