Päivämäärien välisten päivien laskeminen

Yleinen yllätys on, että päivämäärien vähentämisen jälkeen tulossolussa näkyy joskus luvun sijaan päivämäärä, kuten 1/17/1900.

Näin käy, koska solu on perinyt päivämäärämuotoilun. Laskutoimitus on oikein, mutta näyttömuoto on väärä. Korjatkaa tämä vaihtamalla solun muodoksi Luku ilman desimaaleja.

Kun solu on muotoiltu luvuksi, näette odottamanne selkeän päivien määrän.

Pelkkä vähennyslasku laskee päivien väliset välit, ei itse päiviä. Kesäkuun 1. päivästä kesäkuun 18. päivään vähennyslasku antaa tulokseksi 17, mutta molemmat päätepäivät mukaan laskettuna kalenteripäiviä on todellisuudessa 18.

Jos tilanteessa on laskettava mukaan sekä alku- että loppupäivä, lisätkää tulokseen 1.

Siten =B2-A2+1 antaa mukaan lukien lasketun määrän. Päättäkää, kumpaa tapaa raportissanne tarvitaan.

Kokonaisten kuukausien tai vuosien laskeminen vähennyslaskulla on hankalaa. DATEDIF-funktio on suunniteltu juuri sitä varten. Se ottaa kolme argumenttia: alkamispäivämäärän, päättymispäivämäärän ja lainausmerkeissä olevan yksikkökoodin.

Yksikkökoodi määrittää, mitä lasketaan:

• "D" laskee päivät
• "M" laskee täydet kuukaudet
• "Y" laskee täydet vuodet

Huomautus: DATEDIF on piilotettu vanha funktio. Se toimii, mutta Excel ei näytä sitä automaattisessa täydennyksessä.

Käyttäkää "M"-yksikköä kahden päivämäärän välisten kokonaisten kuukausien laskemiseen. Keskeneräisiä kuukausia ei oteta huomioon.

Jos A2 on 15. tammikuuta ja B2 on 10. huhtikuuta, =DATEDIF(A2, B2, "M") palauttaa arvon 2, koska vain kaksi kokonaista kuukautta on kulunut. Kolmas kuukausi ei ole vielä täysi kuukauden 10. päivänä.

Juuri näin mitataan yleensä tilauksen kestoa tai sopimuksen pituutta.

"Y"-yksikkö antaa kokonaisten vuosien määrän, joten se on oikea tapa iän laskemiseen.

Jos A2 sisältää syntymäpäivän, sen vertaaminen tähän päivään antaa henkilön iän täysinä vuosina. Funktio palauttaa oikein pienemmän luvun siihen asti, kunnes syntymäpäivä on kuluvana vuonna ohitettu.

Näin vältetään pyöristysvirheet, joita syntyy jaettaessa päivien määrää 365:llä.

DATEDIF-funktiossa on ylimääräisiä yksikkökoodeja ajanjakson jakamiseen osiin:

• "YM" = kuukaudet, jotka jäävät jäljelle kokonaisten vuosien laskemisen jälkeen
• "MD" = päivät, jotka jäävät jäljelle kokonaisten kuukausien laskemisen jälkeen
• "YD" = päivät, jotka jäävät jäljelle kokonaisten vuosien laskemisen jälkeen

Näiden avulla kesto voidaan ilmaista muodossa 2 vuotta, 3 kuukautta, 5 päivää yhdistämällä kolme DATEDIF-kutsua.

Pelkkä vähennyslasku laskee kaikki kalenteripäivät, myös viikonloput. Kun haluatte laskea vain työpäivät, käyttäkää funktiota NETWORKDAYS.

Se laskee kahden päivämäärän väliset arkipäivät ja mahdollistaa ohitettavien pyhäpäivien luettelon antamisen. Siten =NETWORKDAYS(A2, B2) palauttaa maanantaista perjantaihin osuvien päivien määrän, ja valinnainen kolmas argumentti viittaa pyhäpäivien alueeseen.

Tämä on oikea työkalu toimitusarvioihin ja SLA-aikataulujen laskemiseen.

Oletetaan, että laskun eräpäivä on solussa A2 ja haluatte ilmoittaa, montako päivää lasku on myöhässä tähän päivään verrattuna, mutta näyttää arvon 0, jos eräpäivä ei ole vielä mennyt.

Vähentäkää eräpäivä tästä päivästä ja yhdistäkää tulos sitten funktioon MAX arvolla 0, jotta erääntymättömästä laskusta ei koskaan tule negatiivista lukua.

Tämä yksi kaava antaa selkeän ja järkevän myöhässä olevien päivien määrän perintäraporttia varten.

Kun päivien määrä on tiedossa, voitte muuntaa sen muiksi yksiköiksi. Jos ajanjakso halutaan ilmaista täysinä viikkoina, jakakaa päivien välinen erotus 7:llä ja pyöristäkää alaspäin INT-funktiolla.

Siten =INT((B2-A2)/7) kertoo, montako kokonaista viikkoa kahden päivämäärän väliin mahtuu. 17 päivän välistä tulee 2 viikkoa.

Sama ajatus toimii minkä tahansa yksikön kanssa: jakakaa päivien määrä ja pyöristäkää tulos raportin tarpeiden mukaan.

Opitte useita tapoja mitata ajanjaksoja:

• =B2-A2 antaa päivien välisen eron, kun myöhempi päivämäärä sijoitetaan ensin. Lisäämällä 1 saadaan mukaan lukien laskettu määrä.
• Muotoilkaa tulossolu uudelleen muotoon Luku, jos siinä näkyy päivämäärä.
• DATEDIF(start, end, "D"/"M"/"Y") laskee kokonaiset päivät, kuukaudet tai vuodet. Se sopii erinomaisesti iän ja keston laskemiseen.
• NETWORKDAYS laskee työpäivät ja voi ohittaa pyhäpäivät.
• Käyttäkää muotoa MAX(0, ...) negatiivisten myöhässä olevien päivien määrien välttämiseen.

Olette suorittaneet Date and Time Functions -kurssin.