Excel-kaava: Laske tuntien määrä kahden kerran välillä

Yleinen kaava

=IF(end>start, end-start, 1-start+end)

Yhteenveto

Voit laskea kahden tunnin välisen tuntimäärän käyttämällä kaavaa, joka vähentää aloitusajan lopetusajasta. Jos aloitus- ja lopetusajat ulottuvat keskiyöhön, sinun on muutettava kaavaa alla kuvatulla tavalla. Esitetyssä esimerkissä kaava kohdassa D5 on:

=IF(C5>B5,C5-B5,1-B5+C5)

Selitys

Jos haluat laskea tuntien välisen tunnin, kun molemmat ajat ovat samana kalenteripäivänä, voit vähentää alkamisajan lopetusajasta:

=end-start

Ja käytä numeroiden muotoilua näyttötunteihin.

Kuitenkin, kun ajat ylittävät päivän rajan (keskiyö), asiat voivat muuttua hankaliksi. Lue alla nähdäksesi useita tapoja hallita tätä haastetta.

Kuinka Excel seuraa aikaa

Excelissä yksi päivä on 1, mikä tarkoittaa 24 tuntia. Tämä tarkoittaa, että ajat ja tunnit ovat murto-arvoja 1, kuten alla olevassa taulukossa esitetään:

Tunnit Aika Murtoluku Arvo
3 03:00 3/24 0,125
6 6.00 6/24 0,25
4 04:00 4/24 0,167
8 8.00 AAMULLA 8/24 0,333
12 12:00 12/24 0.5
18 18.00 18/24 0,75
21 21:00 21/24 0,875

Yksinkertainen kestolaskenta

Kun alkamis- ja lopetusaika ovat samana päivänä, keston laskeminen tunteina on suoraviivaista. Esimerkiksi aloitusajan ollessa 9:00 ja lopetusajan 17:00 voit käyttää yksinkertaisesti tätä kaavaa:

=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours

Kun ajat ylittävät keskiyön

Kuluneen ajan laskeminen on hankalampaa, jos ajat ylittävät päivän rajan (keskiyö). Esimerkiksi, jos alkamisaika on yksi päivä kello 22.00 ja lopetusaika seuraavana päivänä kello 5.00, päättymisaika on itse asiassa pienempi kuin alkamisaika ja yllä oleva kaava palauttaa negatiivisen arvon toimisto näyttää hash-merkkijonon (########).

Voit korjata tämän ongelman käyttämällä tätä kaavaa päiviin, jotka ylittävät päivän rajan:

=1-start+end

Vähentämällä alkamisajan arvoksi 1 saat ensimmäisen päivän ajan, jonka voit lisätä vain toisen päivän aikaan, joka on sama kuin lopetusaika .

Tämä kaava ei toimi saman päivän aikoina, joten voimme yleistää ja yhdistää molemmat kaavat IF-lauseen sisällä näin:

=IF(end>start, end-start, 1-start+end)

Nyt kun molemmat ajat ovat samana päivänä, loppu on suurempi kuin alkamisaika , joten käytetään yksinkertaista kaavaa. Mutta kun kertaa päivärajan yli käytetään toista kaavaa.

MOD-toiminto vaihtoehto

Käyttämällä MOD-funktiota jakajalla 1 voimme yksinkertaistaa yllä olevaa kaavaa tähän:

=MOD(end-start,1)

Tässä MOD-toiminto hoitaa negatiivisen ongelman käyttämällä MOD-toimintoa "kääntääkseen" negatiiviset arvot vaadittuun positiiviseen arvoon. Tämä kaavan versio käsittelee molemmat tapaukset, joten voimme poistaa ehdollisen IF-lauseen.

Huomaa: kumpikaan yllä olevista kaavoista ei kestä yli 24 tunnin kestoja. Jos tarvitset tätä, katso päivämäärä + aika -vaihtoehto alla.

Jos haluat lisätietoja modulosta, tässä on hyvä linkki Khan-akatemiaan.

Aikojen muotoilu

Oletusarvoisesti Excel voi näyttää ajan, jopa kestoa edustavan ajan, käyttäen AM / PM. Esimerkiksi, jos laskettu aika on 6 tuntia, Excel voi näyttää tämän kello 6.00. Poista AM / PM soveltamalla mukautettua lukumuotoa, kuten h: mm.

Jos laskettu aika ylittää 24 tuntia, kannattaa käyttää mukautettua muotoa, kuten (h): mm. Hakasulkujen syntaksissa (h) käsketään Excelin näyttämään yli 24 tunnin tuntikestot. Jos et käytä sulkeita, Excel yksinkertaisesti "kaatuu", kun kesto saavuttaa 24 tuntia (kuten kello).

Ongelman yksinkertaistaminen päivämäärällä + kellonaikalla

Voit yksinkertaisesti kuluneen ajan laskemisen ongelman työskentelemällä arvojen kanssa, jotka sisältävät sekä päivämäärän että kellonajan. Voit kirjoittaa päivämäärän ja kellonajan yhdessä käyttämällä yhtä välilyöntiä ajan ja päivämäärän välillä: 1.9.2016 10:00 AM

Sitten voit käyttää peruskaavaa laskea kulunut aika:

=end-start

Alla olevassa esimerkissä alku- ja loppuarvot sisältävät sekä päivämäärät että kellonajat:

Kaava on:

=C5-B5

Alustettu mukautetulla numeromuodolla (h): mm, kuluneiden tuntien näyttämiseksi.

Hyvät linkit

Johdatus modulaariseen matematiikkaan (Khan Academy) Työtuntien laskeminen (Chandoo-video)

Mielenkiintoisia artikkeleita...