Iegūstiet darba laiku starp datumiem un laikiem

Lai aprēķinātu kopējās darba stundas starp diviem datumiem un laikiem, varat izmantot formulu, kuras pamatā ir funkcija NETWORKDAYS. Parādītajā piemērā E5 satur šādu formulu:

=( 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 “zemāks” ir nosaukts diapazons H5 un “augšējais” ir nosauktais diapazons H6.

noapaļo līdz tuvākajam dolāram Excel

Piezīme: šo piemēru iedvesmoja formulas izaicinājums Chandoo un pilnīgāku risinājumu, ko nodrošina formulu meistars Berijs Hudīni forumā MrExcel.

Šī formula aprēķina kopējās darba stundas 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 ir 17:00. Tie formulā parādās kā nosaukti diapazoni “apakšējais” un “augšējais”.

Formulas loģika ir aprēķināt visas iespējamās darba stundas starp sākuma un beigu datumiem, ieskaitot, pēc tam atcelt visas stundas sākuma datumā, kas notiek starp sākuma laiku un zemāku laiku, un visas stundas beigu datumā, kas notiek starp beigu laiks un augšējais laiks.

The NETWORKDAYS funkcija apstrādā brīvdienu un svētku dienu izslēgšanu (ja tas ir norādīts kā datumu diapazons). Jūs varat pārslēgties uz NETWORKDAYS.INTL ja jūsu grafikā ir nestandarta darba dienas.

Izvades formatēšana

Rezultāts ir skaitlis, kas apzīmē kopējās stundas. Tāpat kā visas Excel reizes , jums būs jāformatē izvade ar piemērotu skaitļa formāts . Parādītajā piemērā mēs izmantojam:

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

Kvadrātiekavas neļauj Excel apgāzties, ja stundas ir lielākas par 24. Citiem vārdiem sakot, tās ļauj parādīt stundas, kas ir lielākas par 24. Ja stundām nepieciešama decimāldaļa, varat reiziniet rezultātu ar 24 un formatēt kā parastu skaitli.

Vienkārša versija

Ja sākuma un beigu laiks būs vienmēr starp apakšējo un augšējo laiku, varat izmantot vienkāršāku šīs formulas versiju:

kā aprēķināt indeksu Excel

 
[h]:mm

Nav sākuma laika un beigu laika

Lai aprēķinātu kopējās darba stundas starp diviem datumiem, pieņemot, ka visas dienas ir pilnas darba dienas, varat izmantot vēl vienkāršāku formulu:

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

Skat skaidrojums šeit sīkāku informāciju.