Generieke formule
=(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)
Samenvatting
Om het totale aantal werkuren tussen twee datums en tijden te berekenen, kunt u een formule gebruiken die is gebaseerd op de functie NETWERKDAGEN. In het getoonde voorbeeld bevat E5 deze formule:
=(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)
waarbij "onder" het benoemde bereik H5 is en "boven" het benoemde bereik H6 is.
Opmerking: dit voorbeeld is geïnspireerd door een formule-uitdaging op Chandoo en een meer complete oplossing van formulemeester Barry Houdini op het MrExcel-forum.
Uitleg
Deze formule berekent de totale werkuren tussen twee datums en tijden, die voorkomen tussen een "onder" en "boven" tijd. In het getoonde voorbeeld is de onderste tijd 9:00 uur en de bovenste tijd 17:00 uur. Deze verschijnen in de formule als de benoemde bereiken "onder" en "boven".
De logica van de formule is om alle mogelijke werkuren tussen de start- en einddatums, inclusief, te berekenen en vervolgens alle uren op de startdatum die tussen de starttijd en de lagere tijd vallen, en alle uren op de einddatum die tussen de eindtijd en de bovenste tijd.
De functie NETWERKDAGEN verwerkt de uitsluiting van weekenden en feestdagen (indien opgegeven als een datumbereik). U kunt overschakelen naar NETWORKDAYS.INTL als u niet-standaard werkdagen heeft.
Uitvoer formatteren
Het resultaat is een getal dat het totale aantal uren vertegenwoordigt. Zoals alle Excel-tijden, moet u de uitvoer opmaken met een geschikt getalformaat. In het getoonde voorbeeld gebruiken we:
(h):mm
De vierkante haken voorkomen dat Excel doorrolt wanneer uren groter zijn dan 24. Met andere woorden, ze maken het mogelijk om uren groter dan 24 weer te geven. Als u een decimale waarde nodig heeft voor uren, kunt u het resultaat vermenigvuldigen met 24 en opmaken als een regulier nummer.
Simpele versie
Als begin- en eindtijden altijd tussen de onderste en bovenste tijden voorkomen, kunt u een eenvoudigere versie van deze formule gebruiken:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Geen starttijd en eindtijd
Om de totale werkuren tussen twee datums te berekenen, ervan uitgaande dat alle dagen volledige werkdagen zijn, kunt u een nog eenvoudigere formule gebruiken:
=NETWORKDAYS(start,end,holidays)*hours
Zie uitleg hier voor details.
