Kellonajat Excelissä

Kellonajat Excelissä

Kellonajoilla laskemiseen liittyy muutamia erityispiirteitä ja ennen kaikkea omituisuuksia, jotka on hyvä tietää mikäli sinun pitää laskea Excelissä ajoilla.

Mitä solussa on, jos solussa on 12:00?

Kun syötät soluun kellonajan, syötät soluun tosiasiassa desimaaliluvun väliltä 0 – 1.

Ajat ja arvot

Koska kellonajat ovat desimaalilukuja, niitä voi käyttää laskutoimituksissa. Voit esimerkiksi vähentää päättymisajasta alkamisajan ja saat tulokseksi keston:

laskeminen

Jos aikalaskut antavat omituisia tuloksia, tarkista että tulossolussa on kellonaikamuotoilu.

Yli 24-tuntiset tulokset

Kun lasket kellonajoista summia joiden tulokset ylittävät 24-tuntia, viisari pyörähtää kellossa ympäri ja näyttää sitä kellonaikaa johon viisarit päätyvät. Kun esimerkiksi nollaan eli keskiyöhön lisätään 7:30, 9:00 ja 8:30, aikaa on kulunut 25:00 tuntia eli kello on seuraavana yönä 1:00.

kysymysmerkkinaama

Tosiasiassa Excel on laskenut tunnit yhteen ja esimerkiksi yllä olevan tulossolun arvo on 1,04. Excel vain käyttää tuloksen esittämisessä sellaista kellonaikamuotoilua, joka näyttää aikoja välillä 0:00 – 24:00.

Kun lisäät solun kellonaikamuotoiluun tuntikoodin ympärille hakasulkumerkit, Excel muotoilee solun yli 24-tuntiseen muotoon. Siirry siis muotoiluikkunaan esimerkiksi pikavalikon komennolla Format Cells (Muotoile solut) ja määritä solulle oma (custom) muotoilu käyttämällä koodia [t]:mm. (Jos työasemassasi on yhdysvaltalaiset asetukset, käytä koodia [h]:mm.)

24 yli muotoilu

Negatiiviset kellonajat

Vielä omituisempiin asioihin siirrymme nyt, kun ryhdymme ihmettelemään negatiivisia aikoja. Tottahan on, että aika voi olla vähemmän kuin tavoiteaika ja tuloksena on miinustunteja. Vaan sepä ei olekaan niin yksinkertaista Excelissä.

Kun yrität laskea laskutoimituksen, jonka tuloksena olisi negatiivinen aika, Excel antaa tulokseksi pelkkää risuaitaa. Eikä sarakkeen leventäminen auta, saat vain esiin leveämmän risukon.

negatiiviset

Tämä johtuu siitä, että Excelissä on käytössä aikajärjestelmä, joka ei jostain syystä kykene näyttämään negatiivisia kellonaikoja.

Vaihdat aikajärjestelmän työkirjakohtaisesti komennolla File > Options > Advanced (Tiedosto > Asetukset > Lisäasetukset) ja Use 1904 date system (Käytä 1904-päivämääräjärjestelmää).

1904 vaihtaminen

Ja johan Excel näyttää miinustunnitkin:

erotukset näkyvät

Valitettavasti aikajärjestelmän muuttaminen ei ole ihan niin helppo juttu eikä sitä tietyissä työkirjoissa pysty vaihtamaan.

Normaali Excelin aikajärjestelmä alkaa päivästä 1.1.1900 eli kyseisen päivän sisäinen arvo on 1. Esimerkiksi tämän kirjoituspäivän 3.9.2014 arvo eli järjestysnumero on 41885. On 41885. päivä vuoden 1900 alusta laskettuna. Kun aikajärjestelmän vaihtaa 1904-järjestelmään, työkirjan aika muuttuukin alkamaan päivästä 2.1.1904 eli 2.1.1904 onkin päivä numero 1. Aikajärjestelmän muuttaminen aiheuttaa sen, että kaikki solujen arvot näytetään uuden järjestelmän mukaisesti eli päivämäärät kasvavat tai vähenevät neljällä vuodella ja yhdellä päivällä. Esimerkiksi 3.9.2014 muuttuu päiväksi 4.9.2018, kun 1900- vaihdetaan 1904-järjestelmään.

Älä siis vaihda sellaisen työkirjan aikajärjestelmää, johon on jo syötetty päivämääriä tai joudut syöttämään/muuntamaan päivämäärät. Älä myöskään milloinkaan kopioi päivämääriä kahden eri järjestelmän välisten työkirjojen välillä!

Entäpä jos haluat syöttää soluun negatiivisen ajan? Noh – sehän ei onnistu kummassakaan aikajärjestelmässä. Syötät soluun negatiivisen ajan kirjoittamalla funktion:

=-TIME(12;30;0)
=-AIKA(12;30;0)

negatiivisen tallennus

Ja tämäkin onnistuu vain 1904-järjestelmällä varustetussa työkirjassa.

Muunnokset

Muunnat kellonajan kymmenjärjestelmän luvuksi tai päin vastoin joko kertomalla tai jakamalla 24:llä.

muunnokset

Huomaa! Joudut yleensä muotoilemaan tulossolun joko luku- tai kellonaikamuotoon, jotta tulos näyttää järkevältä.

Eri vuorokausien välisten tuntien laskeminen

Jos laskettavat ajat ovat eri vuorokausina, syötä soluun myös päivämäärä.

päivämäärät mukana

Jos solussa ei ole päivämäärää ja tiedät, että ajat ovat eri vuorokausilta, lisää päättymisaikaan ensin 1 ja vähennä sen jälkeen aloitusaika.

eri vuorokausien ajat

Myös erilaiset IF-funktioratkaisut voivat olla tarpeen. Jos esimeriksi voit olettaa, että mikäli päättymisaika on pienempi kuin aloitusaika, kyseessä on eri vuorokausien ajat:

=IF(B3<B2;B3+1-B2;B3-B2) (käytä suomenkielisessä versiossa funktiota JOS).

Tämä logiikka ei luonnollisesti toimi, jos päättymisaika voi olla seuraavana vuorokautena myöhemmin kuin edellisenä vuorokautena alkanut aika. Sillon todennäköisesti taulukossa on myös päiväys, ja sitä voi käyttää kaavassa hyödyksi.

Ja Nickehän kirjoitti päiväyksiin liittyvistä kaavoista helmikuussa: Päivämäärät ja aikakaavat.

10 kommenttia artikkeliin ”Kellonajat Excelissä

  1. Kiitos hyvistä vinkeistä ajnlaskentaan. Mutta kuitenkin yhteenlaskettu aika näyttää itselläni oudolta eli tuntien kohdlla on pitkä numerosarja 2051403:08:29 Mistä tama muodostuu, ja saa muutettua normaaliksi ajaksi?

    Tykkää

    • Olisi kiinnostava nähdä mistä ajoista summa muodostuu. Voiko olla niin, että laskettavissa soluissa on oikeasti jotain muuta kuin pelkät ajat ja solut on vain muotoiltu näyttämään tunnit ja minuutit?

      Tykkää

  2. Sama homma kuin Kallella eli työajanseurantaa tässä virittelin. Kiitos paljon tästä ohjeesta; on epätodennäköistä että olisin itse keksinyt vastauksen pulmaan 🙂

    Liked by 1 henkilö

  3. Päädyin tänne, kun etsin vastausta ongelmaan. Ajan laskennassa tuli erikoinen ongelma vastaan: Kun laskee erotusta kahden pvm + kellonaika solun välillä (muotoilu p.k.vv t:mm) ja erotuksen laskevan kaavan tulos on tasan 10:00 (eli ei mennä yli 24 t, ne toimivat fine), Excel ei tunnista lukua 10:00:ksi. Jos pvm:n jättää pois ja laskee pelkillä kellonajoilla, toimii ok. Kaikki muut tasatunnit toimivat. Toisesssa solussa on vertailuluku 10:00, josta hakee vertailutietoa erotuksen laskevasta kaavasta (tyyliin jos arvo on 10, saa tietyn arvon, jos yli 10 saa tietyn arvon) – kaikki muut arvot toimivat paitsi tuo 10:00. Jos luvun syöttää käsin, kaava toimii. Täysin mystinen, jotenkin liittyy tuohon päivämäärään, koska ilman toimii, mutten ymmärrä miksi. Mac-kone, O365 uusin versio.

    Tykkää

    • Moi Maria,

      Jos solussa on sekä pvm että kellonaika, solussa on todellisuudessa sekä päivämäärää kuvaava kokonaisosa että kellonaikaa kuvaava desimaaliosa. Koska Excel käsittelee lukuja 15 numeron tarkkuudella (ks. http://excelunplugged.com/2014/05/19/15-digit-limit-in-excel/), desimaaliosat eivät ole täsmälleen samat ilman päivämäärää ja päivämäärän kanssa, jos tarkastellaan riittävää määrää desimaaleja.

      Jos vertaat alla olevan kuvan desimaaliosia, huomaat että ensimmäinen 7:aan pyöristyvä luku on eri kohdassa. Jos siis vertaat jonkun tietyn kaavan tulosta kellonaikaan 10:00, se ei välttämättä ole täsmälleen identtinen. Joudut siis pyöristämään luvut sopivaan määrään desimaaleja ennen kuin vertaat niitä toisiinsa.

      t. Heidi

      Tykkää

  4. Siunattu sivu! Olen tätä negatiivista kellonaikaa pähkäillyt pari vuotta. Minulla on silti edelleen toinenkin ongelma: Puran saneluita ja työnantaja haluaa saneluiden määrän minuutin ja sekuntien tarkkuudella siten, että sekunnit ilmoitetaan kymmenesosina. Onko Excelissä mahdollisuutta pitää tällaista kirjaa, josta voisi tiedot näppärästi siirtää työnantajalle. Lisämausteena se, että pidän itselläni kirjanpitoa ”perinteisen ajanmerkitsemisen tyyliin” eli kun sanelun pituus on 6 min 16 s se merkitään 6:19. Työnantaja haluaa sen muodossa 6,3, kaikki pyöristetään alaspäin, eli jos sanelu olisi 6:23 se olisi tuon saman 6,3.

    Liked by 1 henkilö

    • Hei Pirkko,

      Mukavaa, että juttu ilahdutti. 😉

      Auttaisikohan seuraava kaava sinua pyöristyksissä ja aika->desimaali -konversiossa:
      =PYÖRISTÄ.DES.ALAS((A1-KOKONAISLUKU(A1))*24*60;1) missä oletetaan, että kellonaika on syötetty soluun A1. Tulossolu pitää muotoilla luvuksi, ei kellonajaksi.

      Jos käytät englanninkielistä versiota, kaava on:
      =ROUNDDOWN((A1-INT(A1))*24*60;1)

      Terkuin Heidi

      Tykkää

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s