Generisk formel
=(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)
Sammendrag
For å beregne total arbeidstid mellom to datoer og klokkeslett, kan du bruke en formel basert på NETTVERKDAGER-funksjonen. I eksemplet som vises inneholder E5 denne formelen:
=(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)
hvor "nedre" er det navngitte området H5 og "øvre" er det navngitte området H6.
Merk: dette eksemplet ble inspirert av en formelutfordring på Chandoo, og en mer komplett løsning levert av formelmester Barry Houdini på MrExcel-forumet.
Forklaring
Denne formelen beregner total arbeidstid mellom to datoer og klokkeslett, som oppstår mellom en "nedre" og "øvre" tid. I eksemplet som vises er den nedre tiden 9:00 og den øvre tiden er 17:00. Disse vises i formelen som de nevnte områdene "nedre" og "øvre".
Logikken med formelen er å beregne alle mulige arbeidstimer mellom start- og sluttdatoer, inkludert, og deretter sikkerhetskopiere timer på startdatoen som oppstår mellom starttid og lavere tid, og timer på sluttdatoen som oppstår mellom sluttid og øvre tid.
NETWORKDAYS-funksjonen håndterer ekskludering av helger og helligdager (når det er angitt som en rekke datoer). Du kan bytte til NETWORKDAYS.INTL hvis timeplanen din har ikke-standard arbeidsdager.
Formatering av utdata
Resultatet er et tall som representerer totalt antall timer. Som alle Excel-tider, må du formatere utdataene med et passende tallformat. I det viste eksemplet bruker vi:
(h):mm
De hakeparentesene hindrer Excel i å rulle over når timer er større enn 24. Med andre ord gjør de det mulig å vise timer større enn 24. Hvis du trenger en desimalverdi i flere timer, kan du multiplisere resultatet med 24 og formatere som vanlig nummer.
Enkel versjon
Hvis start- og sluttider alltid vil forekomme mellom nedre og øvre tid, kan du bruke en enklere versjon av denne formelen:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Ingen starttid og sluttid
For å beregne total arbeidstid mellom to datoer, forutsatt at alle dager er fulle arbeidsdager, kan du bruke en enda enklere formel:
=NETWORKDAYS(start,end,holidays)*hours
Se forklaring her for detaljer.
