Päivämäärä ja aikakaavat

Viikonloppuna sitä tuli, lunta nimittäin. =SNOW() Siitä innostuneena pysyttelin sisätiloissa ja päätin istua mieluummin koneen ääressä ja miettiä hieman ajan kulkua.

Excel aloittaa ajanlaskun päivämäärästä 1.1.1900. Tuo hetki on Excelin Big Bang ja sen järjestysnumero on 1. Kaikki päivämäärät ovat Excelin kuoren alla oikeasti vain kokonaislukuja. Kellonajat näiden lukujen desimaaleja. Esimerkiksi tätä kirjoittaessani on 1.2.2014 klo 22:30, eli 41671,94. Siksi Excelillä on helppo laskea myös ajan kanssa. Eilen olisi ollut 41671 - 1 = 41670. Meidän tajuamamme 1.2.2014 on vain solumuotoilu Excelissä.

Itse asiassa, Excelin oletuspäivämääräjärjestelmä Windows-versiossa on tuo yllä kuvailtu. Macissä Big Bang onkin oletuksena 2.1.1904. Molemmat käyttöjärjestelmäversiot ymmärtävät kumpaakin päivämääräjärjestelmää ja se on helposti muutettavissa asetuksella. Mutta jos aiot käsitellä samaa tiedostoa, jossa käsitellään päivämääriä, sekä Windowsissa että Macissa, pitää tämä asia osata ottaa huomioon.


Käsittelen seuraavaksi muutaman kaavan, joita itse käytän

TODAY (TÄMÄ.PÄIVÄ)
Tästä kaikki alkaa. Ainakin tässä artikkelissa. Palauttaa päivämäärän järjestysluvun. Ei ota argumentteja. TODAY:n lisäki on olemassa NOW (NYT), joka toimii muuten samalla tavalla, mutta palauttaa päivämäärän lisäksi myös laskentahetken kellonajan.

Syntaksi
TODAY()
NOW() 

YEAR (VUOSI), MONTH (KUUKAUSI), DAY (PÄIVÄ)
Palauttaa päivämäärää vastaavan vuosiluvun, kuukauden ja kuukauden päivän. Palautettava vuosi on kokonaisluku väliltä 1900–9999, kuukausi välillä 1-12 ja päivä 1-31.

Syntaksi

VUOSI(järjestysnro)

KUUKAUSI(järjestysnro)

PÄIVÄ(järjestysnro) 

Järjestysnro voi olla joko muotoa pp.kk.vvvv tai sitten kaava, esimerkiksi =MONTH(TODAY()) palauttaa helmikuussa luvun 2 ja maaliskuussa 3. Saat taulukkosi rullaamaan automaattisesti ajassa eteenpäin.

WEEKNUM (VIIKKO.NRO)
Palauttaa määritetyn päivämäärän viikon numeron.

Syntaksi

VIIKKO.NRO(järjestysnro;[palauta_tyyppi])

Tässä kohtaa kannattaa olla tarkkana kaavan oikean argumentoinnin kanssa. Funktio tottelee kahta eri järjestelmää.

• Järjestelmä 1    Tammikuun 1. päivän sisältävä viikko on vuoden ensimmäinen viikko, joten sen numero on 1.
• Järjestelmä 2    Vuoden ensimmäisen torstain sisältävä viikko on vuoden ensimmäinen viikko, joten sen numero on 1. Tämä on ISO 8601 ‑standardin mukainen järjestelmä, jota kutsutaan yleisesti eurooppalaiseksi järjestelmäksi.

Lisäksi järjestelmä 1:ssä voidaan viikon ensimmäiseksi päiväksi laittaa mikä tahansa viikonpäivä, jenkeillä esimerkiksi sunnuntai. Itse käytän aina ISO-standardin mukaista viikkonumerointia joka täytyy erikseen määritellä argumentilla "21". Oletusarvolla, tai tyhjällä, viikkonumerointi alkaa järjestelmä yhden mukaisesti ja ensimmäinen päivä on sunnuntai.


EOMONTH (KUUKAUSI.LOPPU)
Palauttaa kuukauden viimeisen päivämäärän järjestysnumeron. Kuukausi on se kuukausi, joka on annetun kuukausimäärän päässä argumentin aloituspäivä määrittämästä päivästä.

Syntaksi

KUUKAUSI.LOPPU(aloituspäivä; kuukaudet)

Molemmat argumentit ovat pakollisia. Kuukaudet on aloituspäivää edeltävien tai seuraavien kuukausien määrä. Kuluvan kuukauden viimeisen päivämäärän saat syöttämällä nollan. Seuraava kuukausi 1, edellinen -1.



Esimerkkejä
Tänään (päivämäärä): =TODAY()
Huomenna (päivämäärä): =TODAY()+1
Eilen (päivämäärä): =TODAY-1
Kuluva kuukausi: =MONTH(TODAY())
Seuraava kuukausi: =MONTH(TODAY())+1
Edellinen kuukausi: =MONTH(TODAY())-1 SEIS!!

Mietitäänpäs hetki tätä viimeistä. MONTH palauttaa vain luvun 1-12:n välillä, ei päivämäärää. Kyllä, nyt helmikuussa toimisi. 2-1=1. Mutta entäs jos onkin tammikuu? 1-1=0. Excel ei osaa rullata aikaa tässä kohtaa tammikuusta joulukuuhun. Mitä tehdä? Käytä yllämainittua EOMONTH -funktiota TODAY:n sijaan. Eli näin:
Kuluva kuukausi: =MONTH(EOMONTH(TODAY();0)
Seuraava kuukausi: =MONTH(EOMONTH(TODAY();1)
Edellinen kuukausi:  =MONTH(EOMONTH(TODAY();-1)

Päivämäärä- ja aikafunktiot -artikkeli Microsoftin Excel-tuessa.

Excelissä on piilossa kaavoja, joita ei ole dokumentoitu. Tai ainakaan Microsoft ei jostain syystä halua niitä varsinaisesti tuoda esille. (Dokumentoimattomuuden toteat aloittamalla kaavan =DATEDIF( ja painamalla VAIHTO+F3. Lisää kaava -ruutu ei näytä kaikkia argumentteja. Vasta sitten kun olet syöttänyt argumentit ja saanut tuloksen ja uudestaan painat VAIHTO+F3, näyttää Excel argumentit.) Alla kaksi niistä, jotka liittyvät aikaan.Tai ainakin ensimmäinen niistä. Toinen vähän löyhästi.

DATEDIF(PVMERO) laskee kahden päivämäärän erotuksen.

Syntaksi
PVMERO(ens_pvm;viim_pvm;yksikkö)


Funktiolla voit näppärästi laskea kahden päivämäärän erotuksen, vaikkapa kuinka monta päivää seuraavaan jouluaattoon tai oman ikäsi.

Ajanjakson ensimmäinen ja viimeinen päivämäärä voi olla joko merkkijono lainausmerkeissä "4.2.2014", järjestysnumero tai kaavalla muodostettu päivämäärä. Pääasia, että se näyttää päivämäärältä eikä osu ajalle ennen 1.1.1900. Yksikkö-argumentilla määritellään merkitseekö päivämäärien vuosiluvut mitään.

Esimerkki
=DATEDIF("4.2.2014";"24.12.2015";"YD") Tulos 323. Vuosiluvulla ei merkitystä.
=DATEDIF("4.2.2014";"24.12.2015";"D") Tulos 688. Vuosiluvulla on merkitys.

PVMERO-artikkeli Microsoftin Excel-tuessa.

ROMAN
Haluatko välttämättä tietää miten kuluva vuosi ilmaistaan roomalaisin numeroin? Kokeile =ROMAN(2014) tai ensi vuonnakin oikein laskevaa ENG: =ROMAN(YEAR(TODAY())  FI: =ROMAN(VUOSI(TÄMÄ.PÄIVÄ()). Ai että miten roomalaiset numerot käännetään arabialaisiksi numeroiksi? No helposti. Syötä soluun A1 roomalainen numero ja soluun B1, riippuen kielesät jompi kumpi näistä:
ENG: =MATCH(A1;INDEX(ROMAN(ROW(INDIRECT("1:4000")));0);0)
FI: =VASTINE(A1;INDEKSI(ROMAN(RIVI(EPÄSUORA("1:4000")));0);0)


Helppoa kuin heinänteko. Eikö? :-)

PS. Vaikka Excelin mielestä maailma alkoi 1.1.1900, niin monella, historiallista tietoa käsittelevällä, on tarve käyttää tuota päivää edeltäviä ajankohtia. Ainoaksi vaihtoehdoksi jää päivämäärien syöttäminen tekstinä. Samalla valitettavasti laskenta tekstillä kaavoissa muuttuu ilman kikkakolmosia mahdottomaksi.