Általános képlet
=(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)
Összegzés
Két dátum és időpont közötti teljes munkaidő kiszámításához használhatja a NETWORKDAYS függvényen alapuló képletet. A bemutatott példában az E5 a következő képletet tartalmazza:
=(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)
ahol az "alsó" a megnevezett H5 tartomány és a "felső" a megnevezett H6 tartomány.
Megjegyzés: ezt a példát egy Chandoo-i képletkihívás inspirálta, és egy teljesebb megoldást Barry Houdini képletmester nyújtott a MrExcel fórumon.
Magyarázat
Ez a képlet kiszámítja az összes munkaidőt két dátum és időpont között, amelyek az "alsó" és a "felső" idő között fordulnak elő. A bemutatott példában az alsó idő 9:00, a felső pedig 17:00. Ezek úgy jelennek meg a képletben, hogy a megnevezett tartományok "alsó" és "felső".
A képlet logikája az, hogy kiszámítja az összes lehetséges munkaidőt a kezdő és a befejező dátum között, ideértve a kezdő és az alacsonyabb időpont közötti esetleges órákat, valamint a befejezés dátumának bármely óráját. a végidő és a felső idő.
A NETWORKDAYS funkció kezeli a hétvégék és ünnepek kizárását (ha dátumtartományként adják meg). Ha az ütemtervének nem szabványos munkanapjai vannak, átválthat a NETWORKDAYS.INTL szolgáltatásra.
A kimenet formázása
Az eredmény egy szám, amely az összes órát jelöli. Mint minden Excel-alkalommal, a kimenetet is megfelelő számformátummal kell formáznia. A bemutatott példában a következőket használjuk:
(h):mm
A szögletes zárójelek megakadályozzák az Excel gördülését, ha az óra meghaladja a 24-et. Más szavakkal, lehetővé teszik a 24-nél nagyobb órák megjelenítését. Ha órákra van szükség tizedesértékre, az eredményt megszorozhatja 24-vel, és formázhatja rendes szám.
Egyszerű változat
Ha a kezdési és a befejezési idő mindig az alsó és a felső idők között következik be, használhatja a képlet egyszerűbb változatát:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Nincs kezdési és befejezési idő
Két dátum közötti teljes munkaidő kiszámításához, feltételezve, hogy minden nap teljes munkanap, akkor még egyszerűbb képletet használhat:
=NETWORKDAYS(start,end,holidays)*hours
A részleteket lásd itt.
