Generička formula
=IF(end>start, end-start, 1-start+end)
Sažetak
Da biste izračunali broj sati između dva puta, možete upotrijebiti formulu koja oduzima vrijeme početka od vremena završetka. Ako se vrijeme početka i završetka kreće u ponoć, morat ćete prilagoditi formulu kako je objašnjeno u nastavku. U prikazanom primjeru formula u D5 je:
=IF(C5>B5,C5-B5,1-B5+C5)
Obrazloženje
Da biste izračunali sate između vremena, kada su oba vremena u istom kalendarskom danu, možete jednostavno oduzeti vrijeme početka od vremena završetka:
=end-start
I primijenite oblikovanje brojeva za prikaz sati.
Međutim, kad vremena prijeđu dnevnu granicu (ponoć), stvari mogu postati nezgodne. Pročitajte u nastavku kako biste vidjeli nekoliko načina za rješavanje ovog izazova.
Kako Excel prati vrijeme
U Excelu je jedan dan jednak 1, što predstavlja 24 sata. To znači da su vremena i sati razlomljene vrijednosti 1, kao što je prikazano u donjoj tablici:
| Sati | Vrijeme | Frakcija | Vrijednost |
|---|---|---|---|
| 3 | 3:00 UJUTRO | 3/24 | 0,125 |
| 6 | 06:00 | 6/24 | 0,25 |
| 4 | 4:00 SATI UJUTRO | 4/24 | 0,167 |
| 8 | 8:00 | 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 |
Jednostavan izračun trajanja
Kad su vrijeme početka i završetka u istom danu, izračunavanje trajanja u satima je jednostavno. Na primjer, s vremenom početka od 9:00 i završetkom od 17:00, možete jednostavno koristiti ovu formulu:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Kad vremena prijeđu ponoć
Izračunavanje proteklog vremena je nezgodnije ako vremena prijeđu dnevnu granicu (ponoć). Na primjer, ako je vrijeme početka 22:00 jedan dan, a vrijeme završetka 5:00 AM sljedeći dan, vrijeme završetka zapravo je manje od vremena početka, a gornja formula vratit će negativnu vrijednost, a Excel keper prikazuje niz hash znakova (########).
Da biste riješili ovaj problem, ovu formulu možete koristiti za vremena koja prelaze dnevnu granicu:
=1-start+end
Oduzimanjem vremena početka od 1 dobivate količinu vremena u prvom danu, koju jednostavno možete dodati količini vremena u drugom danu, što je jednako vremenu završetka .
Ova formula neće raditi vremena u istom danu, tako da možemo generalizirati i kombinirati obje formule unutar naredbe IF na sljedeći način:
=IF(end>start, end-start, 1-start+end)
Sada, kad su oba vremena u istom danu, kraj je veći od vremena početka , pa se koristi jednostavna formula. Ali kada se prekorači vrijeme preko dana, koristi se druga formula.
Alternativa funkcije MOD-a
Korištenjem MOD funkcije s djeliteljem 1, gornju formulu možemo pojednostaviti na ovo:
=MOD(end-start,1)
Ovdje se MOD funkcija brine o negativnom problemu koristeći MOD funkciju za "prebacivanje" negativnih vrijednosti na traženu pozitivnu vrijednost. Ova verzija formule obrađivat će oba slučaja, tako da možemo ukloniti uvjetni izraz IF.
Napomena: nijedna gornja formula neće podnijeti trajanja dulja od 24 sata. Ako vam je ovo potrebno, pogledajte donju opciju za datum i vrijeme.
Za više informacija o modulu, evo dobre poveznice na Khan Academy.
Oblikovanje vremenskih trajanja
Prema zadanim postavkama Excel može prikazati vrijeme, čak i vrijeme koje predstavlja trajanje, koristeći AM / PM. Na primjer, ako imate izračunato vrijeme od 6 sati, Excel to može prikazati kao 06:00. Da biste uklonili AM / PM, primijenite prilagođeni format broja poput h: mm.
U slučajevima kada izračunato vrijeme prelazi 24 sata, možda ćete htjeti koristiti prilagođeni format poput (h): mm. Sintaksa kvadratnih zagrada (h) govori Excelu da prikaže trajanje sata duže od 24 sata. Ako ne koristite zagrade, Excel će se jednostavno "prevrnuti" kada trajanje dosegne 24 sata (poput sata).
Pojednostavljivanje problema s datumom i vremenom
Problem izračunavanja proteklog vremena možete jednostavno riješiti radom s vrijednostima koje sadrže i datum i vrijeme. Da biste zajedno unijeli datum i vrijeme, koristite jedan razmak između vremena i datuma: 1.9.2016. 10:00
Tada možete koristiti osnovnu formulu za izračunavanje proteklog vremena:
=end-start
U donjem primjeru vrijednosti početka i kraja sadrže i datume i vrijeme:
Formula je:
=C5-B5
Formatiran s prilagođenim formatom broja (h): mm, za prikaz proteklih sati.
