Yleinen kaava
=(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)
Yhteenveto
Voit laskea kahden päivämäärän ja kellonajan välisen kokonaistyötunnin NETWORKDAYS-funktioon perustuvan kaavan avulla. Esitetyssä esimerkissä E5 sisältää tämän kaavan:
=(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)
missä "alempi" on nimetty alue H5 ja "ylempi" on nimetty alue H6.
Huomaa: tämä esimerkki on saanut inspiraationsa Chandoon kaavahaasteesta ja kattavammasta ratkaisusta, jonka kaavamestari Barry Houdini toimitti MrExcel-foorumilla.
Selitys
Tämä kaava laskee kahden päivämäärän ja kellonajan välisen kokonaistyöajan, joka tapahtuu "alemman" ja "ylemmän" ajan välillä. Esitetyssä esimerkissä alempi aika on 9:00 ja ylin aika 17:00. Nämä esiintyvät kaavassa nimettyinä alueina "alempi" ja "ylempi".
Kaavan logiikka on laskea kaikki mahdolliset työajat alkamis- ja lopetuspäivien välillä, mukaan lukien, ja sitten perua aloituspäivän tunnit, jotka esiintyvät aloitusajan ja alemman ajan välillä, ja lopettaa päivämäärän kaikki tunnit, jotka tapahtuvat välillä päättymisaika ja ylin aika.
NETWORKDAYS-toiminto käsittelee viikonloppujen ja pyhäpäivien poissulkemisen (jos päivämääräalue). Voit siirtyä NETWORKDAYS.INTL-tilaan, jos aikataulussasi on epätyypillisiä työpäiviä.
Alustetaan lähtö
Tulos on luku, joka edustaa kokonaistunteja. Kuten kaikki Excel-ajat, joudut muotoilemaan tuloksen sopivalla lukumuodolla. Esitetyssä esimerkissä käytämme:
(h):mm
Hakasulkeet estävät Excelin kaatumisen, kun tunnit ovat yli 24. Toisin sanoen, ne mahdollistavat yli 24-tuntien näyttämisen. Jos tarvitset desimaaliarvoa tunneille, voit kertoa tuloksen 24: llä ja muotoilla säännöllinen numero.
Yksinkertainen versio
Jos aloitus- ja lopetusajat ovat aina ala- ja yläaikojen välillä, voit käyttää tämän kaavan yksinkertaisempaa versiota:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Ei alkamis- ja lopetusaikaa
Jos haluat laskea kahden päivämäärän välisen kokonaistyötunnin, olettaen, että kaikki päivät ovat kokonaisia työpäiviä, voit käyttää vielä yksinkertaisempaa kaavaa:
=NETWORKDAYS(start,end,holidays)*hours
Katso lisätietoja täältä.
