Izračunajte vrijeme u Excelu (vremenska razlika, odrađeni sati, zbrajanje/ oduzimanje)

Budući da su datumi i vremena pohranjeni kao brojevi na stražnjoj strani programa Excel, možete jednostavno koristiti jednostavne aritmetičke operacije i formule na vrijednostima datuma i vremena.

Na primjer, možete dodati dvije različite vrijednosti vremena ili vrijednosti datuma ili možete izračunati vremensku razliku između dva navedena vremena.

U ovom vodiču pokazat ću vam nekoliko načina izvođenja izračuna pomoću vremena u Excelu (poput izračunavanja vremenske razlike, zbrajanja ili oduzimanja vremena, prikazivanja vremena u različitim formatima i zbroja vrijednosti vremena)

Kako Excel postupa s datumom i vremenom?

Kao što sam spomenuo, datumi i vremena pohranjeni su kao brojevi u ćeliji u Excelu. Cijeli broj predstavlja cijeli dan, a decimalni dio broja predstavlja dio dana (koji se može pretvoriti u vrijednosti sati, minuta i sekundi)

Na primjer, vrijednost 1 predstavlja 01. siječnja 1900. u Excelu, što je početna točka od koje Excel počinje razmatrati datume.

Dakle, 2 bi značilo 02. siječnja 1990., 3 bi značilo 03. siječnja 1900. i tako dalje, a 44197 značilo bi 1. siječnja 2021.

Napomena: Excel za Windows i Excel za Mac slijede različite datume početka. 1 u Excelu za Windows značilo bi 1. siječnja 1900., a 1 u Excelu za Mac značilo bi 1. siječnja 1904. godine

Ako u ovim brojevima postoje znamenke iza decimalne točke, Excel bi ih smatrao dijelom dana i može se pretvoriti u sate, minute i sekunde.

Na primjer, 44197,5 bi značilo 1. siječnja 2021. u 12:00:00.

Dakle, ako radite s vrijednostima vremena u Excelu, u osnovi biste radili s decimalnim dijelom broja.

Excel vam daje fleksibilnost pretvaranja tog decimalnog dijela u različite formate, kao što su samo sati, samo minute, samo sekunde ili kombinacija sati, minuta i sekundi

Sada kada razumijete kako se vrijeme pohranjuje u Excelu, pogledajmo neke primjere kako izračunati vremensku razliku između dva različita datuma ili vremena u Excelu

Formule za izračunavanje vremenske razlike između dva vremena

U mnogim slučajevima, sve što želite učiniti je saznati ukupno vrijeme koje je proteklo između dvostrukih vrijednosti (na primjer u slučaju vremenske tablice koja ima vrijeme u vremenu i vrijeme izvan vremena).

Metoda koju odaberete ovisit će o tome kako je vrijeme navedeno u ćeliji i u kojem formatu želite rezultat.

Pogledajmo nekoliko primjera

Jednostavno oduzimanje izračuna vremenske razlike u Excelu

Budući da je vrijeme pohranjeno kao broj u Excelu, pronađite razliku između 2 vrijednosti vremena, vrijeme početka možete lako oduzeti od vremena završetka.

Vrijeme završetka - vrijeme početka

Rezultat oduzimanja također bi bila decimalna vrijednost koja bi predstavljala vrijeme koje je proteklo između dvije vremenske vrijednosti.

Ispod je primjer gdje imam vrijeme početka i završetka te sam izračunao vremensku razliku jednostavnim oduzimanjem.

Postoji mogućnost da se vaši rezultati prikažu u vremenskom formatu (umjesto decimalnih mjesta ili u vrijednostima sati/minuta). U našem gornjem primjeru, rezultat u ćeliji C2 prikazuje 09:30 umjesto 9,5.

To je sasvim u redu jer Excel pokušava kopirati format iz susjednog stupca.

Da biste ovo pretvorili u decimalni broj, promijenite format ćelija u Općenito (opcija je na kartici Početna u grupi Brojevi)

Nakon što dobijete rezultat, možete ga oblikovati na različite načine. Na primjer, vrijednost možete prikazati samo u satima ili samo u minutima ili u kombinaciji sati, minuta i sekundi.

Ispod su različiti formati koje možete koristiti:

A ako se pitate gdje i kako primijeniti ove prilagođene formate datuma, slijedite korake u nastavku:

1. Odaberite ćelije u koje želite primijeniti format datuma
2. Držite tipku Control i pritisnite tipku 1 (ili Command + 1 ako koristite Mac)
3. U dijaloškom okviru Oblikovanje ćelija koji se otvori kliknite karticu Broj (ako već nije odabrana)
4. U lijevom oknu kliknite Prilagođeno
5. Unesite bilo koji željeni kôd formata u polje Vrsta (u ovom primjeru koristim hh: mm: ss)
6. Pritisnite U redu

Gore navedeni koraci promijenili bi oblikovanje i prikazali vrijednost na temelju formata.

Napominjemo da oblikovanje prilagođenog broja ne mijenja vrijednost u ćeliji. Mijenja samo način prikaza vrijednosti. Dakle, mogu odabrati da se samo prikazuje vrijednost sata u ćeliji, dok bi i dalje imala izvornu vrijednost.

Savjet stručnjaka: Ako ukupan broj sati premašuje 24 sata, umjesto toga upotrijebite sljedeći format prilagođenog broja: [hh]: mm: ss

Izračunajte vremensku razliku u satima, minutama ili sekundama

Kada oduzmete vrijednosti vremena, Excel vraća decimalni broj koji predstavlja rezultirajuću vremensku razliku.

Budući da svaki cijeli broj predstavlja jedan dan, decimalni dio broja predstavljao bi onaj dio dana koji se lako može pretvoriti u sate, minute ili sekunde.

Izračunavanje vremenske razlike u satima

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati broj sati između dvostrukih vrijednosti

Ispod je formula koja će vam dati vremensku razliku u satima:

= (B2-A2)*24

Gornja formula će vam dati ukupan broj sati proteklih između dvostrukih vrijednosti.

Ponekad Excel pokušava biti od pomoći i dati će vam rezultat u vremenskom formatu (kao što je prikazano u nastavku).

To možete lako pretvoriti u format broja klikom na karticu Početna, a u grupi Broj odaberite format kao format.

Ako samo želite vratiti ukupan broj sati proteklih između dva puta (bez decimalnog dijela), upotrijebite donju formulu:

= INT ((B2-A2)*24)

Napomena: Ova formula radi samo kada su obje vrijednosti vremena istog dana. Ako se dan promijeni (gdje je jedna od vremenskih vrijednosti drugog datuma, a druga drugog datuma), ova će formula dati pogrešne rezultate. Pogledajte odjeljak u kojem pokrivam formulu za izračun vremenske razlike kada se datum kasnije promijeni u ovom vodiču.

Izračunavanje vremenske razlike u minutama

Da biste izračunali vremensku razliku u minutama, morate pomnožiti dobivenu vrijednost s ukupnim brojem minuta u danu (što je 1440 ili 24*60).

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati ukupan broj minuta koji je protekao između datuma početka i završetka.

Ispod je formula koja će to učiniti:

= (B2-A2)*24*60

Izračunavanje vremenske razlike u sekundama

Da biste izračunali vremensku razliku u sekundama, morate pomnožiti dobivenu vrijednost s ukupnim brojem sekundi u danu (što je ili 24*60*60 ili 86400).

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati ukupan broj sekundi koje su protekle između datuma početka i završetka.

Ispod je formula koja će to učiniti:

= (B2-A2)*24*60*60

Izračunavanje vremenske razlike pomoću funkcije TEXT

Drugi jednostavan način za brzo dobivanje vremenske razlike bez brige o promjeni formata je korištenje funkcije TEXT.

Funkcija TEXT omogućuje vam da navedete format izravno unutar formule.

= TEXT (Datum završetka - Datum početka, format)

Prvi argument je izračun koji želite napraviti, a drugi argument format u kojem želite prikazati rezultat izračuna.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati vremensku razliku između dva puta.

Evo nekoliko formula koje će vam dati rezultat s različitim formatima

Prikaži samo broj sati:

= TEKST (B2-A2, "hh")

Gornja formula će vam dati samo rezultat koji prikazuje broj sati proteklih između dvostrukih vrijednosti. Ako je vaš rezultat 9 sati i 30 minuta, i dalje će vam prikazivati ​​samo 9.

Prikažite ukupan broj minuta

= TEKST (B2-A2, "[mm]")

Prikažite ukupan broj sekundi

= TEKST (B2-A2, "[ss]")

Prikaži sate i minute

= TEKST (B2-A2, "[hh]: mm")

Prikaži sate, minute i sekunde

= TEKST (B2-A2, "hh: mm: ss")

Ako se pitate koja je razlika između hh i [hh] u formatu (ili mm i [mm]), kada koristite uglate zagrade, to će vam dati ukupan broj sati između dva datuma, čak i ako vrijednost sata veća je od 24. Dakle, ako oduzmete dvije vrijednosti datuma gdje je razlika veća od 24 sata, upotrebom [hh] dobit ćete ukupan broj sati, a hh će vam dati samo sate protekle na dan završnog datuma .

Dobijte vremensku razliku u jednoj jedinici (sati/minute) i zanemarite druge

Ako želite izračunati vremensku razliku između dviju vremenskih vrijednosti samo u broju sati ili minuta ili sekundi, tada možete koristiti namjenske funkcije SAT, MINUTA ili DRUGA.

Svaka od ovih funkcija uzima jedan jedini argument, što je vrijednost vremena, i vraća navedenu jedinicu vremena.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati ukupan broj sati i minuta koje su protekle između ova dva vremena.

Ispod su formule za to:

Izračunavanje sati proteklo između dva puta

= SAT (B2-A2)

Izračunavanje minuta iz rezultata vrijednosti vremena (isključujući završene sate)

= MINUTA (B2-A2)

Izračunavanje sekundi od rezultata vrijednosti vremena (isključujući završene sate i minute)

= DRUGO (B2-A2)

Nekoliko stvari koje morate znati prilikom rada s ovim formulama HOURS, MINUTE i SECOND:

• Razlika između vremena završetka i vremena početka ne može biti negativna (što je često slučaj kada se datum promijeni). U takvim bi slučajevima ove formule vratile #NUM! pogreška
• Ove formule koriste samo vremenski dio rezultirajuće vrijednosti vremena (a zanemaruju dnevni dio). Dakle, ako je razlika u vremenu završetka i vremenu početka 2 dana, 10 sati, 32 minute i 44 sekunde, formula HOUR će dati 10, formula MINUTE će dati 32, a DRUGA formula 44

Izračunajte proteklo vrijeme do sada (od početka)

Ako želite izračunati ukupno vrijeme koje je proteklo između vremena početka i trenutnog vremena, možete upotrijebiti formulu SADA umjesto vremena završetka.

NOW funkcija vraća trenutni datum i vrijeme u ćeliji u kojoj se koristi. To je jedna od onih funkcija koja ne uzima nikakve ulazne argumente.

Dakle, ako želite izračunati ukupno vrijeme koje je proteklo između vremena početka i trenutnog vremena, možete upotrijebiti donju formulu:

= SADA () - Vrijeme početka

Ispod je primjer gdje imam početna vremena u stupcu A, a do sada proteklo vrijeme u stupcu B.

Ako je razlika u vremenu između datuma početka i vremena i trenutnog vremena veća od 24 sata, tada možete oblikovati rezultat tako da prikazuje dan, kao i dio vremena.

To možete učiniti pomoću formule TEKST ispod:

= TEKST (SADA ()-A2, "dd hh: ss: mm")

Istu stvar možete postići i promjenom prilagođenog oblikovanja ćelije (kao što je opisano ranije u ovom vodiču) tako da prikazuje dan, kao i dio vremena.

U slučaju da vaše vrijeme početka ima samo dio vremena, Excel bi to smatrao vremenom 1. siječnja 1990. godine.

U ovom slučaju, ako koristite funkciju SADA za izračunavanje proteklog vremena, to će vam dati pogrešan rezultat (jer bi rezultirajuća vrijednost imala i ukupne dane koji su protekli od 1. siječnja 1990.).

U tom slučaju možete koristiti sljedeću formulu:

= SADA ()- INT (ODMAH ())- A2

Gornja formula koristi funkciju INT za uklanjanje dnevnog dijela iz vrijednosti koju vraća funkcija now, a to se zatim koristi za izračun vremenske razlike.

Imajte na umu da je SAD nestabilna funkcija koja se ažurira kad god dođe do promjene na radnom listu, ali se ne ažurira u stvarnom vremenu

Izračunajte vrijeme pri promjeni datuma (izračunajte i prikažite negativna vremena u Excelu)

Dosadašnje metode dobro funkcioniraju ako je vaše vrijeme završetka kasnije od vremena početka.

No problem nastaje kada je vaše vrijeme završetka niže od vremena početka. To se često događa kada ispunjavate vremenske listove u koje unosite samo vrijeme, a ne cijeli datum i vrijeme.

U takvim slučajevima, ako radite u jednoj noćnoj smjeni, a datum se mijenja, postoji mogućnost da bi vaše vrijeme završetka bilo ranije od vremena početka.

Na primjer, ako rad započnete u 18:00 navečer, a posao završite u 9:00 ujutro.

Ako radite samo s vrijednostima vremena, oduzimanjem vremena početka od vremena završetka dobit ćete negativnu vrijednost od 9 sati (9 - 18).

Excel ne može podnijeti negativne vrijednosti vremena (a što se toga tiče niti ljudi, osim ako ne možete putovati kroz vrijeme)

U takvim slučajevima trebate način da shvatite da se dan promijenio i da se prema tome treba izvršiti izračun.

Srećom, postoji jako jednostavno rješenje za to.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku, gdje ja imam vrijeme početka i vrijeme završetka.

Kao što biste primijetili da je ponekad vrijeme početka navečer, a vrijeme završetka ujutro (što znači da je ovo bila noćna smjena i dan se promijenio).

Ako za izračun vremenske razlike koristim donju formulu, pokazat će mi se znakovi raspršivanja u ćelijama gdje je rezultat negativna vrijednost (označeno žutom bojom na donjoj slici).

= B2-A2

Evo IF formule koja, bez obzira na to je li vrijednost vremenske razlike negativna ili ne, i u slučaju da je negativna, vraća pravi rezultat

= IF ((B2-A2) <0,1- (A2-B2), (B2-A2))

Iako ovo dobro funkcionira u većini slučajeva, ipak nedostaje u slučaju da vrijeme početka i vrijeme završetka budu udaljeni više od 24 sata. Na primjer, netko se prijavljuje u 9:00 sati 1. dana, a odjava u 11:00 sati 2. dana.

Budući da je ovo više od 24 sata, ne postoji način da se zna je li se osoba odjavila nakon 2 sata ili nakon 26 sati.

Iako bi najbolji način za rješavanje ovoga bio osigurati da unosi uključuju datum i vrijeme, ali ako je to samo vrijeme s kojim radite, gornja formula trebala bi se pobrinuti za većinu problema ( s obzirom da je malo vjerojatno da će itko raditi duže od 24 sata)

Zbrajanje/ oduzimanje vremena u Excelu

Do sada smo vidjeli primjere gdje smo imali vrijeme početka i završetka i morali smo pronaći vremensku razliku.

Excel vam također omogućuje jednostavno dodavanje ili oduzimanje fiksne vrijednosti vremena od postojeće vrijednosti datuma i vremena.

Na primjer, recimo da imate popis zadataka u redu gdje svaki zadatak traje navedeno vrijeme i želite znati kada će svaki zadatak završiti.

U tom slučaju možete jednostavno dodati vrijeme koje će svaki zadatak oduzeti vremenu početka kako biste znali u koje vrijeme se očekuje da će zadatak biti dovršen.

Budući da Excel pohranjuje vrijednosti datuma i vremena kao brojeve, morate osigurati da vrijeme koje pokušavate dodati poštuje format koji Excel već slijedi.

Na primjer, ako datumu u Excelu dodate 1, dobit ćete sljedeći datum. To je zato što 1 predstavlja cijeli dan u Excelu (što je jednako 24 sata).

Dakle, ako želite dodati 1 sat postojećoj vrijednosti vremena, ne možete nastaviti i jednostavno joj dodati 1. morate se pobrinuti da vrijednost sata pretvorite u decimalni dio koji predstavlja jedan sat. a isto vrijedi i za zbrajanje minuta i sekundi.

Korištenje funkcije TIME

Funkcija vremena u Excelu uzima vrijednost sata, minute i sekunde te ih pretvara u decimalni broj koji predstavlja ovo vrijeme.

Na primjer, ako želim dodati 4 sata postojećem vremenu, mogu upotrijebiti donju formulu:

= Vrijeme početka + VRIJEME (4,0,0)

Ovo je korisno ako znate koliko sati, minuta i sekundi želite dodati postojećem vremenu i jednostavno koristite funkciju VRIJEME bez brige o ispravnoj pretvorbi vremena u decimalnu vrijednost.

Također imajte na umu da će funkcija TIME uzeti u obzir samo cijeli broj vrijednosti sata, minuta i sekundi koje unesete. Na primjer, ako u funkciji TIME koristim 5,5 sati, dodalo bi se samo pet sati i zanemario decimalni dio.

Također imajte na umu da funkcija TIME može dodati samo vrijednosti koje su manje od 24 sata. Ako je vaša vrijednost sata veća od 24, to bi vam dalo netočan rezultat.

Isto vrijedi i za minute i drugi dio gdje će funkcija uzeti u obzir samo vrijednosti koje su manje od 60 minuta i 60 sekundi

Baš kao što sam dodao vrijeme pomoću funkcije TIME, možete i oduzeti vrijeme. Samo promijenite znak + u negativan znak u gornjim formulama

Korištenje osnovne aritmetike

Kad je vremenska funkcija jednostavna i prikladna za upotrebu, dolazi s nekoliko ograničenja (kao što je gore opisano).

Ako želite veću kontrolu, možete upotrijebiti aritmetičku metodu koju ću ovdje obraditi.

Koncept je jednostavan - pretvorite vrijednost vremena u decimalnu vrijednost koja predstavlja dio dana, a zatim je možete dodati bilo kojoj vrijednosti vremena u Excelu.

Na primjer, ako želite dodati 24 sata postojećoj vrijednosti vremena, možete upotrijebiti donju formulu:

= Vrijeme početka + 24/24

To samo znači da dodajem jedan dan postojećoj vrijednosti vremena.

Napredujući s istim konceptom, recimo da želite dodati 30 sati vremenskoj vrijednosti, možete upotrijebiti donju formulu:

= Vrijeme početka + 30/24

Gornja formula čini istu stvar, gdje bi cijeli broj (30/24) predstavljao ukupan broj dana u vremenu koje želite dodati, a decimalni dio bi predstavljao sate/minute/sekunde

Slično, ako imate određeni broj minuta koje želite dodati vrijednosti vremena, možete upotrijebiti donju formulu:

= Start_time + (minute za dodavanje)/24*60

A ako imate broj sekundi koje želite dodati, tada možete upotrijebiti donju formulu:

= Start_time + (minute za dodavanje)/24*60*60

Iako ova metoda nije tako jednostavna kao korištenje funkcije vremena, smatram je puno boljom jer djeluje u svim situacijama i slijedi isti koncept. za razliku od funkcije vremena, ne morate brinuti o tome je li vrijeme koje želite dodati manje od 24 sata ili više od 24 sata

Možete slijediti isti koncept oduzimajući i vrijeme. Samo promijenite znak + u negativan predznak u gornjim formulama

Kako zbrojiti vrijeme u Excelu

Ponekad ćete možda htjeti brzo zbrojiti sve vremenske vrijednosti u Excelu. Dodavanje više vremenskih vrijednosti u Excel prilično je jednostavno (potrebna je samo jednostavna formula ZBOR)

No postoji nekoliko stvari koje morate znati kada dodate vrijeme u Excel, točnije format ćelije koji će vam pokazati rezultat.

Pogledajmo primjer.

U nastavku imam popis zadataka zajedno s vremenom koje će svaki zadatak oduzeti u stupcu B i želim brzo dodati ta vremena i znati ukupno vrijeme koje će trebati za sve te zadatke.

U ćeliji B9 koristio sam jednostavnu formulu SUM za izračun ukupnog vremena koje će svi ovi zadaci potrajati, a ona mi daje vrijednost kao 18:30 (što znači da će za dovršenje trebati 18 sati i 20 minuta svi ti zadaci)

Za sada sve dobro!

Kako zbrajati više od 24 sata u Excelu

Sada vidi što se događa kad promijenim vrijeme u kojemu će zadatak 2 biti dovršen od 1 sata do 10 sati.

Rezultat sada kaže 03:20, što znači da bi trebalo proći 3 sata i 20 minuta za izvršavanje svih ovih zadataka.

To je netočno (očito)

Ovdje problem nije u tome da Excel zabrlja. Problem je u tome što je ćelija oblikovana na takav način da će vam pokazati samo vremenski dio dobivene vrijednosti.

A budući da je ovdje dobivena vrijednost veća od 24 sata, Excel je odlučio pretvoriti dio od 24 sata u dan ukloniti ga iz vrijednosti koja se prikazuje korisniku i prikazati samo preostale sate, minute i sekunde.

Na sreću, ovo se lako rješava.

Sve što trebate učiniti je promijeniti format ćelije kako bi se prikazalo radno vrijeme čak i ako prelazi 24 sata.

U nastavku se nalaze neki formati koje možete koristiti:

Format možete promijeniti tako da odete u dijaloški okvir ćelija formata i primijenite prilagođeni format ili upotrijebite funkciju TEXT i upotrijebite bilo koji od gore navedenih formata u samoj formuli

Možete koristiti donju formulu TEXT za prikaz vremena, čak i ako je više od 24 sata:

= TEKST (ZBIR (B2: B7), "[h]: mm: ss")

ili donju formulu ako sate duže od 24 sata želite pretvoriti u dane:

= TEKST (ZBIR (B2: B7), "dd hh: mm: ss")

Rezultati koji prikazuju raspršivanje (###) umjesto datuma/vremena (razlozi + rješenje)

U nekim slučajevima možete otkriti da umjesto prikaza vrijednosti vremena Excel prikazuje simbole raspršivanja u ćeliji.

Evo nekoliko mogućih razloga i načina za njihovo rješavanje:

Stupac nije dovoljno širok

Kad ćelija nema dovoljno prostora za prikaz cijelog datuma, može prikazati simbole raspršivanja.

Jednostavno ga je popraviti - promijenite širinu stupca i učinite ga širim.

Negativna vrijednost datuma

Vrijednost datuma ili vremena ne može biti negativna u Excelu. u slučaju da računate vremensku razliku, a ona se pokaže negativnom, Excel će vam prikazati simbole raspršivanja.

Način popravka za promjenu formule kako biste dobili pravi rezultat. Na primjer, ako računate vremensku razliku između dva vremena i datum se mora promijeniti, morate prilagoditi formulu tako da to uzme u obzir.

U drugim slučajevima možete koristiti funkciju ABS za pretvaranje negativne vrijednosti vremena u pozitivan broj kako bi se ispravno prikazala. Alternativno, također možete formulom IF provjeriti je li rezultat negativna vrijednost i vratiti nešto značajnije.

U ovom vodiču obrađivao sam teme o računanju vremena u Excelu (gdje možete izračunati vremensku razliku, dodati ili oduzeti vrijeme, prikazati vrijeme u različitim formatima i zbrojiti vrijeme)

Nadam se da vam je ovaj vodič bio koristan.