Tietokoneiden kanssa puuhatessa on kaksi asiaa, jotka aiheuttavat lähes aina ongelmia. Päivämäärät ja skandinaaviset aakkoset. Tällä kertaa ihmetellään hieman ensimmäisiä.

Minulla on Excelissä seuraavanlainen taulukko.

Voin lisätä B-sarakkeeseen arvoja seuraavissa formaateissa ja Excel ymmärtää tarkoittavani päivämäärää ja aikaa.

  • 9/25/2024 8:25
  • 09/25/2024 8:25
  • 9-25-2024 8:25
  • 09-25-2024 8:25
  • 9/25/2024 8:25:06 AM
  • 09/25/2024 8:25:06 AM
  • 9-25-2024 8:25:06 AM
  • 09-25-2024 8:25:06 AM
  • jne

Tämä tietenkin jos Excelin asetuksissa muotoiluksi on valittu Englanti. Vaihdetaan muotoiluksi Suomi.

Nyt käytettävä muotoilu vaihtuu ja päivämäärät aikoineen tulee syöttää eri muodossa.

Päivämäärän (ja ajan) sisäinen tallennusmuoto Excelissä

Haetaan saman Excel-tiedoston taulukon rivit Power Automatella.

Päivämäärät aikoineen esitetetäänkin nyt numeroina. Mitäs tämä nyt on.

Kyllä. Excel käyttää päivämäärän ja ajan sisäisenä tallennusmuotona numeroa. Se kertoo, miten kauan hetkestä 0.1.1900 klo 00:00:00.0000000 on kestänyt kyseiseen hetkeen. Numerot ovat käteviä, koska niiden välillä voi tehdä nopeasti laskutoimituksia.

Esimerkiksi numero 45560.3506944444 koostuu kahdesta osasta

  • 45560 = Montako päivää on on kulunut kuvitteellisesta päivästä 0.1.1900.
  • 0.3506944444 = Miten suuri osa vuorokaudesta on kulunut

Käytännössä tämä numero vastaa aikaa 25.9.2024 klo 8:25:00.

Mutta miten päivämäärän voi muuttaa numeroksi ja päinvastoin?

Power Automate: Numeron muuntaminen päivämääräksi

Ryan Maclean on kirjoittanut mainion blogipostauksen, jossa kerrotaan miten numero muutetaan takaisin päivämääräksi.

Kaava on seuraava.

addseconds('1899-12-30',int(formatnumber(mul(float('45560.3506944444'),86400),'0')))

Käytännössä numero kerrotaan vuorokaudessa olevien sekuntien määrällä (86400) ja se lisätään päivämärään 30.12.1899. Miksi sitä ei lisätä päivään 1.1.1900? Se selviää lukemalla Ryanin blogipostaus.

Compose-toiminnossa käyttämämme esimerkkikoodi antaa oikean lopputuloksen.

Mikäli käsittelemme ainoastaan päivämäärää, on kaava yksinkertaisempi. 

addDays('1899-12-30',int('45560'))

Power Automate: Päivämäärän muuntaminen numeroksi

Miten muunnos tehdään toiseen suuntaan?

Power Automatesta löytyy ticks() -funktio, joka palauttaa päivämäärää vastaavaan numeron suhteessa päivään 1.1.0001.

Päivämäärän numero saadaankin helposti laskemalla halutun päivän ja päivän 30.12.1899 ero tickseinä ja jakamalla se sopivalla määrää sekunninosia.

div(sub(ticks('2024-09-25'),ticks('1899-12-30')),864000000000)

Lopputulos näyttää oikealta.

Samaan lopputulokseen päästään laskemmalla päivämäärien ero (päivinä). Tulos on tyypiltään timespan string ja se sisältää päivien lisäksi myös minuutit tunnit ja sekunnit. Tämän vuoksi jaetaan se osiin (split) ja käytetään lopputuloksesta vain ensimmäistä osaa (päiviä).

split(dateDifference('1899-12-30','2024-09-25'),'.')[0]

Entäpä numeron loppuosa? Osuus kuluvasta päivästä. Jaetaan tunnit, minuuti ja sekunnit kukin erikseen lukumäärällä, joita niitä on vuorokaudessa. Lopuksi lasketaan tulokset yhteen.

Esimerkiksi kellonaika 8:25:00 lasketaan seuraavasti

(8/24) + (25/1440) + (00/86400) = 0,3333333333333333 + 0,0173611111111111 + 0  = 0,3506944444444444

Hieman pitää vain pyöristää, sillä 2024-09-25T08:25:00.0000000 vastaava sarjanumero on 45560.3506944444.

Tehdään lopuksi kaava, joka laskee numeron annetulle päivämäärälle ja ajalle. Asetetaan haluttu aika compose-toiminnon (composeDateTime) arvoksi. numeron laskenta ja pyöristykset onnistuu seuraavasti.

substring(string(
  add(
    int(split(dateDifference(
      '1899-12-30',
      formatDateTime(outputs('composeDateTime'),'yyyy-MM-dd')
      ),'.')[0]
    ),
    add(
      add(div(float(formatDateTime(outputs('composeDateTime'),'HH')),24),
        div(float(formatDateTime(outputs('composeDateTime'),'mm')),1440)
        ),
        div(float(formatDateTime(outputs('composeDateTime'),'ss')),86400)
      )
    )
),0,16)

Lähtöarvolla 2024-09-25T08:25:00.0000000 lopputulos on oikea.

Yhteenveto

Mitä hyötyä tämän kaiken ymmärtämisestä on?

Useimmiten tuskin mitään. Power Automatessa, voit määritellä Excelin palauttamaan päivämäärät ISO 8601 -muodossa.

Jolloin päivämäärät ovat tutumman näköisiä.

Myös uuden rivin lisäämisessä, voit määritellä käytetäänkö numeroita vai ISO 8601 -muotoilua.