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)
Resumé
For at beregne den samlede arbejdstid mellem to datoer og tidspunkter kan du bruge en formel baseret på NETVÆRKDAG-funktionen. I det viste eksempel indeholder E5 denne formel:
=(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 navngivne område H5 og "øvre" er det navngivne område H6.
Bemærk: dette eksempel blev inspireret af en formeludfordring på Chandoo og en mere komplet løsning leveret af formelmester Barry Houdini på MrExcel-forummet.
Forklaring
Denne formel beregner den samlede arbejdstid mellem to datoer og tidspunkter, der forekommer mellem en "nedre" og "øvre" tid. I det viste eksempel er den nedre tid 9:00 og den øverste tid er 17:00. Disse vises i formlen som de navngivne områder "nedre" og "øverste".
Logikken med formlen er at beregne alle mulige arbejdstimer mellem start- og slutdatoer inklusive, derefter bakke alle timer på startdatoen, der opstår mellem starttid og lavere tid, og eventuelle timer på slutdatoen, der finder sted mellem sluttid og øvre tid.
NETWORKDAYS-funktionen håndterer udelukkelse af weekender og helligdage (når de er angivet som en række datoer). Du kan skifte til NETWORKDAYS.INTL, hvis din tidsplan ikke har standard arbejdsdage.
Formatering af output
Resultatet er et tal, der repræsenterer det samlede antal timer. Som alle Excel-tider skal du formatere output med et passende nummerformat. I det viste eksempel bruger vi:
(h):mm
De firkantede parenteser forhindrer Excel i at rulle over, når timer er større end 24. Med andre ord gør de det muligt at vise timer større end 24. Hvis du har brug for en decimalværdi i timer, kan du gange resultatet med 24 og formatere som almindeligt nummer.
Enkel version
Hvis start- og sluttider altid vil forekomme mellem nedre og øvre tidspunkter, kan du bruge en enklere version af denne formel:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Ingen starttid og sluttid
For at beregne den samlede arbejdstid mellem to datoer, forudsat at alle dage er fulde hverdage, kan du bruge en endnu enklere formel:
=NETWORKDAYS(start,end,holidays)*hours
Se forklaring her for detaljer.
