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.
Koska kellonajat ovat desimaalilukuja, niitä voi käyttää laskutoimituksissa. Voit esimerkiksi vähentää päättymisajasta alkamisajan ja saat tulokseksi keston:
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.
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.)
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.
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ää).
Ja johan Excel näyttää miinustunnitkin:
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)
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ä.
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ä.
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.
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.
HExcelligent.fi 
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ää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ääTykkää
Suuret kiitokset! Hyödylisiä vinkkejä. Sain työajan seurannan toimimaan näiden vinkkien avulla!! 🙂
TykkääLiked by 1 henkilö
Mukava kuulla! 🙂
TykkääTykkää
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 🙂
TykkääLiked by 1 henkilö
Kiitti Sari viestistäsi! Mukava kuulla, että ohjeesta on hyötyä. Kellonajoilla laskemiseen liittyy melko erikoisia piirteitä. 🙂
TykkääTykkää
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ää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ääTykkää
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.
Tykkää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ääTykkää
Pulmaattiseksi tilanne Excelillä menee kun yrittää luoda graafista esitystä esim. juoksukilpailun tuloksista vuosien varrelta. Ajat kun ovat muodossa: minuuttia sekunttia ja sekunnin sadasosia. Olisiko sinulla tähän vinkkiä?
TykkääTykkää
Moi Tapani,
Kerrotko vielä hieman tarkemmin millaisen kaavion haluaisit muodostaa? 😉
t. Heidi
TykkääTykkää
Hei,
mikähän on, kun excel laskee tunnit miten sattuu?
Eli kyseessä yli 24 h. Yhteenlasketut summat haen muista välilehdistä. Tulokseksi tulee 1357:05 tuntia vaikka tuloksen pitäisi olla 181:05.
Muut toimii normaalisti, mutta heti kun menee yli 24 h, niin excel laskee siihen jotain ylimää-räistä. Olen muuttanut solun, niin että näyttää yli 24 h.
Viereen syötän samat tiedot ja lasken niin toimii, mutta kun hakee muista välilehdistä niin ei toimi enää.
TykkääTykkää
Epäilen, että muilla välilehdillä olevissa soluissa on jotain muuta kuin mitä oletat niissä olevan. Tutki solujen todellista sisältöä esimerkiksi Formulas > Show Formulas (Kaavat > Näytä kaavat) -komennolla.
TykkääTykkää
Moi,
Kovin on haastavaa saada toimimaan online excelissä(kin) negatiivisia kellonaikoja… Osaatko sanoa, onko edes mahdollista? Tuota aikajärjestelmän vaihtoa en sieltä löydä.
TykkääTykkää
Hei! Hienoa, että on tällaisia vinkkisivustoja. Minun ongelmani työajan laskennassa on ilta ja yölisien laskenta. Minulla on kahdessa sarakkeessa työajan alkaminen ja seuraavassa työajan päättyminen ja niistä saan laskettua yhteistunnit. Mutta sitten minun pitäisi saada vielä laskettua klo 18-22 väliset tunnit iltalisätunneiksi ja klo 22-06 väliset tunnit yölisätunneiksi ja vielä jos mahdollista niin sunnuntaipäiviltä vastaavat tunnit erikseen (sunnuntaityö, sunnuntai-ilta ja sunnuntaiyö).
TykkääTykkää
Moikka!
Monta ongelmaa olen jo blogin avulla ratkaissut, mutta yhtä mietin vielä. Onko mahdollista tuntilistaa tehdessä saada tehdyistä tunneista vähennettyä ruokatunnin suoraan pois siten, että alle 7h päivästä sitä ei vähennetä kun sellaiseen päivään sitä ei kuulu? Normi päivän kaavan tein B1-A1-0,0125
TykkääTykkää
Tuntien merkitseminen siten että saadaan minuuteilla lasku kaava tehty.
Eli esimerkkinä merkattu 12:00 toiseen soluun tulee -0:06 joka tekisi kaavalla soluun 11:54 ? How can i do that?
TykkääTykkää