Jäljellä olevan ajan laskeminen

Päivämääriä hallinnoidessa on usein tarpeellista selvittää erilaisten ajankohtien etäisyys toisistaan. Sopimuksien jäljellä oleva kesto, aika laskun eräpäivään tai jäljellä oleva aika ihmisten syntymäpäiviin ovat esimerkkejä seurattavista asioista, jotka ovat yksinkertaisia laskea taulukossa.

Tekninen info: Excelissä päivämäärä esitetään tavanomaisesti muodossa 1.10.2017, mutta todellisuudessa se ei tallennu sellaisena taulukkoon. Excelissä laskeminen lähtee päivämäärästä 0.1.1900, joka saa arvon 0. Päivämäärä 1.10.2017 on 43009 päivää päivämäärän 0.1.1900 jälkeen, joten 1.10.2017 tallennetaan taulukkoon lukuna 43009.

Koska Excel tallentaa päivämäärän lukuna, päivämääriä voi vähentää toisistaan ja päivämääriin voi lisätä lukuja. Sitä hyödynnetään tämän artikkelin esimerkeissä.

Lataa tästä esimerkkitiedosto (Aikaa jäljellä.xlsx)

Yksinkertainen esimerkki: kaava


Kuva 1: Yksinkertainen kaava

Excelissä jäljellä oleva aika on helpointa laskea hyödyntämällä funktiota TÄMÄ.PÄIVÄ. Sillä on ainoastaan yksi yksinkertainen tarkoitus: se antaa kuluvan päivän päivämäärän. Se ei tarvitse sisäänsä mitään muuttujia, jonka takia laskennassa kaarisulkeet laitetaan heti kiinni: TÄMÄ.PÄIVÄ(). Funktio lukee päivämäärän tietokoneen kellosta, joten se on aina ajantasainen.

Laskennassa käytetty matematiikka on seuraava: Eräpäivän pvm – Kuluvan päivän pvm = Jäljellä olevat päivät.

Esimerkki: jos eräpäivä on 9.10.2017 ja kuluva päivä on 1.10.2017, Excel laskee 43017 – 43009 = 8.

Eräpäivä on esimerkissä sarakkeessa Päivämäärä ja kuluva päivä saadaan funktiolla TÄMÄ.PÄIVÄ().

Lisähuomio: Jäljellä olevat viikot saadaan, kun jäljellä olevat päivät jakaa seitsemällä. Jäljellä olevat kuukaudet saa vastaavasti jakamalla jäljellä olevat päivät kolmellakymmenellä. Tarkkuus on silloin tarpeeseen nähden riittävä.

Yksinkertainen esimerkki: värit


Kuva 2: Yksinkertaiset värit

Mikäli seurannan päivämäärät eivät ole nousevassa järjestyksessä, väreillä voi helpottaa silmää erottamaan toisistaan suuret ja pienet luvut. Kuvan esimerkissä lukujoukon pienin arvo värjätään punaiseksi ja suurin vihreäksi.

Värialue tulkitsee kaikkia sen sisältämiä lukuja tuottaessaan automaattisia värejä. Tästä johtuen jokainen sarake on värjättävä erikseen. Jos maalaa kaikki taulukon sarakkeet, viikkojen ja kuukausien arvot värjäytyvät liiaksi keltaisella ja punaisella. Kuvan esimerkissä väriasteikot on siis valittu Päiviä jäljellä, Viikkoja jäljellä ja Kuukausia jäljellä -sarakkeille erikseen.

Edistynyt esimerkki: kaava


Kuva 3: Edistynyt kaava

Kaavaa on mahdollista helposti laajentaa hieman, jotta erääntyneet rivit saa eroteltua joukosta. Tämä ei ole välttämätön laajennus, mutta se parantaa taulukon luettavuutta.

Kaava rakentuu seuraavalla logiikalla:

Jos päivämäärien erotus on nollaa pienempi, laitetaan kirjaukseksi Erääntynyt. Muuten esitetään jäljellä olevat päivät.
-> JOS rivin Päivämäärä – kuluva päivä < 0, kirjataan Erääntynyt, muuten rivin Päivämäärä – kuluva päivä
-> JOS ( @PäivämääräTÄMÄ.PÄIVÄ() < 0  ;  “Erääntynyt”  ;  @PäivämääräTÄMÄ.PÄIVÄ() )

Mikäli halutaan laskea jäljellä olevia viikkoja tai kuukausia, jaetaan viimeinen vähennyslasku seitsemällä tai kolmellakymmenellä (katso esimerkkitiedosto).

Edistynyt esimerkki: värit


Kuva 4: Edistyneet värit

Värit on mahdollista asettaa myös hieman johdonmukaisemmiksi. Tässä esimerkissä on valittu, että puolta vuotta pidemmällä olevat päivämäärät ovat kaikki vihreitä ja sitä pienemmät luvut menevät tasaisesti vihreästä keltaisen kautta punaiseen.

Kuvan värjäyssäännöissä on lisäksi päätetty värjätä kaikki erääntyneet ruudut harmaiksi. Fontin väriä voi tarvittaessa vaihtaa Muotoile solut valikon välilehdeltä Fontti. Esimerkkitiedostossa fontti on värjätty valkoiseksi. Edellisen esimerkin tapaan värjäyssäännöt pitää tehdä jokaiselle sarakkeelle muuten erikseen, mutta Erääntynyt-säännön voi tehdä koko taulukolle yhteiseksi.

Lataa tästä esimerkkitiedosto (Aikaa jäljellä.xlsx)

Lopuksi

Excelissä päivämääriä ei voi vähentää siten, että kirjoittaisi kaavan muodossa: =9.10.2017-1.10.2017, sillä Excel ei osaa tulkita sellaista merkintää. Päivämäärät tulee kirjoittaa omiin soluihinsa ja laskenta tulee suorittaa omissa soluissaan, kuten tämän artikkelin esimerkeissä on tehty.

Esimerkkitiedostossa on Päivämäärä-sarakkeessa hyödynnetty TÄMÄ.PÄIVÄ funktiota, jotta esimerkkitiedoston ulkoasu olisi latauspäivästä riippumatta esimerkkikuvien kaltainen.

Katso myös toinen päivämääriin liittyvä artikkeli: Rivien värjääminen päivämäärän mukaan.

 


Caduseus Consulting avustaa ja kouluttaa monenlaisissa IT-tarpeissa!

Mikäli Excel-työ takkuilee ja haluatte ammattilaisapua jonkin projektin kanssa, voitte tilata asiantuntijapalvelua hintaan 45 €/h (+ alv). Kaikista toimeksiannoista voidaan antaa myös kiinteähintainen kokonaistarjous.

Katso myös hintalaskuri edullisille tilauskoulutuksille ja tilaa organisaatiollenne suosittu Excel helpoksi työelämässä-koulutus.


 
 

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *