Izračunajte čas v Excelu (časovna razlika, opravljene ure, seštevanje/ odštevanje)

Ker so datumi in časi shranjeni kot številke na zadnji strani v Excelu, lahko preprosto uporabite aritmetične operacije in formule za vrednosti datuma in časa.

Dodate lahko na primer dve različni časovni vrednosti ali datumske vrednosti ali pa izračunate časovno razliko med dvema časoma.

V tej vadnici vam bom pokazal nekaj načinov za izračun časa v Excelu (na primer izračun časovne razlike, dodajanje ali odštevanje časa, prikaz časa v različnih oblikah in seštevanje časovnih vrednosti)

Kako Excel obravnava datum in uro?

Kot sem že omenil, so datumi in časi shranjeni kot številke v celici v Excelu. Celo število predstavlja celoten dan, decimalni del števila pa del dneva (ki ga je mogoče pretvoriti v vrednosti ur, minut in sekund)

Vrednost 1 na primer predstavlja 1. januar 1900 v Excelu, kar je izhodišče, od katerega Excel začne razmišljati o datumih.

Torej bi 2 pomenilo 2. januar 1990, 3 bi pomenilo 3. januar 1900 in tako naprej, 44197 pa bi pomenilo 1. januar 2021.

Opomba: Excel za Windows in Excel za Mac sledita različnim začetnim datumom. 1 v Excelu za Windows bi pomenilo 1. januarja 1900 in 1 v Excelu za Mac bi pomenilo 1. januarja 1904

Če so v decimalnih mestih za decimalno vejico kakšne števke, jih Excel upošteva kot del dneva in jih je mogoče pretvoriti v ure, minute in sekunde.

44197,5 bi na primer pomenilo 1. januar 2021 ob 12:00:00.

Če torej delate s časovnimi vrednostmi v Excelu, bi v bistvu delali z decimalnim delom števila.

Excel vam omogoča prilagodljivost pretvorbe tega decimalnega dela v različne oblike, na primer samo ure, samo minute, samo sekunde ali kombinacijo ur, minut in sekund

Zdaj, ko razumete, kako je čas shranjen v Excelu, poglejmo nekaj primerov, kako izračunati časovno razliko med dvema različnima datumoma ali časom v Excelu

Formule za izračun časovne razlike med dvema časoma

V mnogih primerih je vse, kar želite storiti, ugotoviti skupni čas, ki je pretekel med dvema vrednostma (na primer v primeru časovnega lista, ki ima čas in čas izteka).

Izbrana metoda je odvisna od tega, kako je čas omenjen v celici in v kakšni obliki želite rezultat.

Oglejmo si nekaj primerov

Enostavno odštevanje izračuna časovne razlike v Excelu

Ker je čas v Excelu shranjen kot številka, poiščite razliko med dvema vrednostma časa, lahko začetni čas enostavno odštejete od končnega časa.

Končni čas - začetni čas

Rezultat odštevanja bi bila tudi decimalna vrednost, ki bi predstavljala čas, ki je pretekel med dvema časovnima vrednostima.

Spodaj je primer, ko imam začetni in končni čas in sem s preprostim odštevanjem izračunal časovno razliko.

Obstaja možnost, da bodo vaši rezultati prikazani v časovnem formatu (namesto decimalk ali v urah/minutah). V našem zgornjem primeru je rezultat v celici C2 namesto 9.5 prikazan 09:30.

To je povsem v redu, saj Excel poskuša kopirati obliko iz sosednjega stolpca.

Če želite to pretvoriti v decimalko, spremenite obliko celic v Splošno (možnost je na zavihku Domov v skupini Številke)

Ko dobite rezultat, ga lahko oblikujete na različne načine. Vrednost lahko na primer prikažete samo v urah ali samo minutah ali v kombinaciji ur, minut in sekund.

Spodaj so različni formati, ki jih lahko uporabite:

In če se sprašujete, kje in kako uporabiti te oblike datumov po meri, sledite spodnjim korakom:

1. Izberite celice, v katere želite uporabiti obliko datuma
2. Držite tipko Control in pritisnite tipko 1 (ali Command + 1, če uporabljate Mac)
3. V pogovornem oknu Oblikovanje celic, ki se odpre, kliknite zavihek Številka (če še ni izbrano)
4. V levem podoknu kliknite Po meri
5. V polje Type vnesite poljubno kodo želenega formata (v tem primeru uporabljam hh: mm: ss)
6. Kliknite V redu

Zgornji koraki bi spremenili oblikovanje in prikazali vrednost glede na obliko.

Upoštevajte, da oblikovanje številk po meri ne spremeni vrednosti v celici. Spremeni le način prikaza vrednosti. Tako se lahko odločim, da bo v celici prikazana samo vrednost ure, medtem ko bo še vedno imela prvotno vrednost.

Nasvet profesionalca: Če skupno število ur presega 24 ur, namesto tega uporabite naslednji format številke po meri: [hh]: mm: ss

Izračunajte časovno razliko v urah, minutah ali sekundah

Ko odštejete časovne vrednosti, Excel vrne decimalno število, ki predstavlja nastalo časovno razliko.

Ker vsako celo število predstavlja en dan, bi decimalni del števila predstavljal tisti del dneva, ki ga je mogoče enostavno pretvoriti v ure ali minute ali sekunde.

Izračun časovne razlike v urah

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati število ur med dvema vrednostma

Spodaj je formula, ki vam bo dala časovno razliko v urah:

= (B2-A2)*24

Zgornja formula vam bo dala skupno število ur, ki so pretekle med dvema vrednostma.

Včasih je Excel v pomoč in vam bo dal tudi rezultat v časovni obliki (kot je prikazano spodaj).

To lahko preprosto pretvorite v obliko številke tako, da kliknete zavihek Domov in v skupini Številka izberete Številka kot obliko.

Če želite vrniti samo skupno število ur, ki so pretekle med dvema časoma (brez decimalnega dela), uporabite spodnjo formulo:

= INT ((B2-A2)*24)

Opomba: Ta formula deluje le, če sta obe časovni vrednosti istega dne. Če se dan spremeni (kjer je ena od časovnih vrednosti drugega datuma, druga pa drugega datuma), bo ta formula dala napačne rezultate. Oglejte si razdelek, kjer zajemam formulo za izračun časovne razlike, ko se datum kasneje spremeni v tej vadnici.

Izračun časovne razlike v minutah

Če želite izračunati časovno razliko v minutah, morate nastalo vrednost pomnožiti s skupnim številom minut na dan (kar je 1440 ali 24*60).

Recimo, da imate niz podatkov, kot je prikazano spodaj, in želite izračunati skupno število minut, ki so pretekle med začetnim in končnim datumom.

Spodaj je formula, ki bo to naredila:

= (B2-A2)*24*60

Izračun časovne razlike v sekundah

Če želite izračunati časovno razliko v sekundah, morate nastalo vrednost pomnožiti s skupnim številom sekund v enem dnevu (kar je ali 24*60*60 ali 86400).

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati skupno število sekund, ki so pretekle med začetnim in končnim datumom.

Spodaj je formula, ki bo to naredila:

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

Izračun časovne razlike s funkcijo TEXT

Drug preprost način, da hitro pridobite časovno razliko, ne da bi skrbeli za spremembo oblike, je uporaba funkcije TEXT.

Funkcija TEXT vam omogoča, da v formuli določite obliko.

= TEXT (Končni datum - začetni datum, oblika)

Prvi argument je izračun, ki ga želite narediti, drugi argument pa je oblika, v kateri želite prikazati rezultat izračuna.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati časovno razliko med tema dvema časoma.

Tukaj je nekaj formul, ki vam bodo dale rezultat z različnimi oblikami

Pokažite samo število ur:

= BESEDILO (B2-A2, "hh")

Zgornja formula vam bo dala le rezultat, ki prikazuje število ur, ki so pretekle med dvema vrednostma. Če je vaš rezultat 9 ur in 30 minut, vam bo še vedno pokazal samo 9.

Pokažite skupno število minut

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

Pokažite skupno število sekund

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

Pokaži ure in minute

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

Pokaži ure, minute in sekunde

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

Če se sprašujete, kakšna je razlika med hh in [hh] v formatu (ali mm in [mm]), bi pri uporabi oglatih oklepajev dobili skupno število ur med obema datumoma, tudi če urna vrednost je večja od 24. Torej, če odštejete dve datumski vrednosti, pri katerih je razlika več kot 24 ur, boste z uporabo [hh] dobili skupno število ur, hh pa samo ur, ki so pretekle na dan končnega datuma .

Pridobite časovno razliko v eni enoti (ure/minute) in zanemarite druge

Če želite časovno razliko med dvema časovnima vrednostma izračunati le v številu ur ali minut ali sekund, lahko uporabite namenske funkcije URA, MINUTA ali DRUGI.

Vsaka od teh funkcij sprejme en sam argument, ki je časovna vrednost, in vrne podano časovno enoto.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati skupno število ur, minut in sekund, ki so pretekle med tema dvema časoma.

Spodaj so formule za to:

Izračun ur, ki so pretekle med dvema časoma

= URA (B2-A2)

Izračun minut iz časovne vrednosti (brez opravljenih ur)

= MINUTA (B2-A2)

Izračun sekund iz rezultata časovne vrednosti (brez opravljenih ur in minut)

= DRUGI (B2-A2)

Pri delu s temi formulami HOURS, MINUTE in SECOND morate vedeti nekaj stvari:

• Razlika med končnim časom in začetnim časom ne more biti negativna (kar se pogosto zgodi, ko se datum spremeni). V takih primerih bi te formule vrnile #NUM! napaka
• Te formule uporabljajo le časovni del nastale časovne vrednosti (in prezrejo dnevni del). Torej, če je razlika v končnem času in začetnem času 2 dni, 10 ur, 32 minut in 44 sekund, bo formula HOUR dala 10, formula MINUTE bo dala 32, druga formula pa 44

Izračunajte pretečeni čas do zdaj (od začetnega časa)

Če želite izračunati skupni čas, ki je pretekel med začetnim časom in trenutnim časom, lahko namesto končnega časa uporabite formulo ZDAJ.

NOW funkcija vrne trenutni datum in čas v celici, v kateri se uporablja. To je ena tistih funkcij, ki ne vzame nobenega vhodnega argumenta.

Torej, če želite izračunati skupni čas, ki je pretekel med začetnim časom in trenutnim časom, lahko uporabite spodnjo formulo:

= ZDAJ () - Začetni čas

Spodaj je primer, ko imam začetne čase v stolpcu A, čas, ki je do sedaj pretekel v stolpcu B.

Če je časovna razlika med začetnim datumom in časom ter trenutnim časom več kot 24 ur, lahko rezultat oblikujete tako, da bo prikazan dan in čas.

To lahko storite s spodnjo formulo BESEDILO:

= BESEDILO (ZDAJ ()-A2, "dd hh: ss: mm")

Enako lahko dosežete tudi s spreminjanjem oblikovanja celice po meri (kot je opisano prej v tej vadnici), tako da prikazuje dan in čas.

Če ima vaš začetni čas samo časovni del, bi Excel to upošteval kot čas 1. januarja 1990.

V tem primeru, če uporabite funkcijo ZDAJ za izračun časa, ki je pretekel do sedaj, bo to povzročilo napačen rezultat (saj bi nastala vrednost imela tudi skupne dni, ki so pretekle od 1. januarja 1990).

V tem primeru lahko uporabite naslednjo formulo:

= ZDAJ ()- INT (ZDAJ ())- A2

Zgornja formula uporablja funkcijo INT za odstranitev dnevnega dela iz vrednosti, ki jo vrne funkcija now, in se nato uporabi za izračun časovne razlike.

Upoštevajte, da je NOW nestanovitna funkcija, ki se posodobi vsakič, ko pride do spremembe na delovnem listu, vendar se ne posodobi v realnem času

Izračunajte čas, ko se datum spremeni (izračunajte in prikažite negativne čase v Excelu)

Dosedanje metode dobro delujejo, če je vaš končni čas poznejši od začetnega.

Težava pa nastane, ko je vaš končni čas krajši od začetnega. To se pogosto zgodi, ko polnite časovne liste, kjer vnesete samo čas, ne pa celotnega datuma in časa.

V takih primerih, če delate v eni nočni izmeni in se datum spremeni, obstaja možnost, da bi bil vaš končni čas prej kot začetni.

Na primer, če začnete z delom ob 18.00 zvečer in dokončate delo in čas odmora ob 9.00 zjutraj.

Če delate samo s časovnimi vrednostmi, boste z odštevanjem začetnega časa od končnega dobili negativno vrednost 9 ur (9 - 18).

Excel ne more obvladovati negativnih časovnih vrednosti (in glede tega tudi ljudje, razen če lahko potujete skozi čas)

V takih primerih potrebujete način, da ugotovite, da se je dan spremenil, in izračun je treba ustrezno izvesti.

Na srečo je to res enostavno popraviti.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, kjer imam začetni in končni čas.

Kot bi opazili, je včasih začetni čas zvečer, končni pa zjutraj (kar kaže, da je bila to nočna izmena in se je dan spremenil).

Če za izračun časovne razlike uporabim spodnjo formulo, mi bo pokazal znake razpršitve v celicah, kjer je rezultat negativna vrednost (označeno z rumeno na spodnji sliki).

= B2-A2

Tukaj je formula IF, če je vrednost časovne razlike negativna ali ne, in v primeru, da je negativna, vrne pravi rezultat

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

Čeprav to v večini primerov dobro deluje, še vedno primanjkuje, če sta začetni in končni čas več kot 24 ur narazen. Na primer, nekdo se prvi dan prijavi ob 9:00, drugi dan pa ob 11:00.

Ker je to več kot 24 ur, ni mogoče ugotoviti, ali se je oseba odjavila po 2 urah ali po 26 urah.

Medtem ko bi bil najboljši način za reševanje tega, da se prepričate, da vnosi vključujejo datum in čas, če pa gre le za čas, s katerim delate, bi morala zgornja formula poskrbeti za večino težav ( ker je malo verjetno, da bi kdo delal več kot 24 ur)

Dodajanje/ odštevanje časa v Excelu

Doslej smo videli primere, ko smo imeli začetni in končni čas, zato smo morali najti časovno razliko.

Excel vam omogoča tudi enostavno dodajanje ali odštevanje fiksne časovne vrednosti od obstoječe vrednosti datuma in časa.

Na primer, recimo, da imate seznam opravil v čakalnem redu, kjer vsako opravilo traja določen čas, in želite vedeti, kdaj se bo vsako opravilo končalo.

V tem primeru lahko preprosto dodate čas začetka vsake naloge, da ugotovite, kdaj naj bi bila naloga opravljena.

Ker Excel shrani vrednosti datuma in časa kot številke, morate zagotoviti, da čas, ki ga poskušate dodati, ustreza obliki, ki ji Excel že sledi.

Če na primer datumu v Excelu dodate 1, vam bo dal naslednji datum. To je zato, ker 1 predstavlja cel dan v Excelu (kar je enako 24 uram).

Če torej želite obstoječi časovni vrednosti dodati 1 uro, ne morete iti naprej in ji preprosto dodati 1. prepričati se morate, da vrednost te ure pretvorite v decimalni del, ki predstavlja eno uro. enako velja za dodajanje minut in sekund.

Uporaba funkcije TIME

Časovna funkcija v Excelu vzame urno vrednost, minuto in sekundo ter jo pretvori v decimalno število, ki predstavlja ta čas.

Na primer, če želim obstoječemu času dodati 4 ure, lahko uporabim spodnjo formulo:

= Začetni čas + TIME (4,0,0)

To je uporabno, če veste, koliko ur, minut in sekund želite dodati obstoječemu času in preprosto uporabite funkcijo ČAS, ne da bi skrbeli za pravilno pretvorbo časa v decimalno vrednost.

Upoštevajte tudi, da bo funkcija TIME upoštevala le celoštevilni del ure, minute in sekunde, ki ga vnesete. Če na primer v funkciji TIME uporabim 5,5 ure, bi dodala le pet ur in prezrla decimalni del.

Upoštevajte tudi, da lahko funkcija TIME dodaja le vrednosti, ki so manjše od 24 ur. Če je vrednost vaše ure večja od 24, bi to pomenilo napačen rezultat.

Enako velja za minute in drugi del, kjer bo funkcija upoštevala le vrednosti, ki so manjše od 60 minut in 60 sekund

Tako kot sem dodal čas s funkcijo TIME, lahko tudi odštejete čas. V zgornjih formulah samo spremenite znak + v negativen znak

Uporaba osnovne aritmetike

Ko je časovna funkcija enostavna in priročna za uporabo, ima nekaj omejitev (kot je opisano zgoraj).

Če želite več nadzora, lahko uporabite aritmetično metodo, ki jo bom opisal tukaj.

Koncept je preprost - pretvorite časovno vrednost v decimalno vrednost, ki predstavlja del dneva, nato pa jo lahko dodate kateri koli časovni vrednosti v Excelu.

Če želite na primer obstoječi časovni vrednosti dodati 24 ur, lahko uporabite spodnjo formulo:

= Start_time + 24/24

To samo pomeni, da obstoječi časovni vrednosti dodam en dan.

Zdaj, ko gremo naprej po istem konceptu, recimo, da želite časovni vrednosti dodati 30 ur, lahko uporabite spodnjo formulo:

= Start_time + 30/24

Zgornja formula naredi isto, kjer bi celoštevilčni del (30/24) predstavljal skupno število dni v času, ki ga želite dodati, decimalni del pa bi predstavljal ure/minute/sekunde

Podobno, če imate določeno število minut, ki jih želite dodati časovni vrednosti, lahko uporabite spodnjo formulo:

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

Če imate število sekund, ki jih želite dodati, lahko uporabite spodnjo formulo:

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

Čeprav ta metoda ni tako enostavna kot uporaba časovne funkcije, se mi zdi veliko boljša, ker deluje v vseh situacijah in sledi istemu konceptu. za razliko od časovne funkcije vam ni treba skrbeti, ali je čas, ki ga želite dodati, manjši od 24 ur ali več kot 24 ur

Istemu konceptu lahko sledite tudi pri odštevanju časa. V zgornjih formulah samo spremenite znak + v minus

Kako sešteti čas v Excelu

Včasih boste morda želeli hitro sešteti vse časovne vrednosti v Excelu. Dodajanje več časovnih vrednosti v Excelu je precej preprosto (potrebna je le preprosta formula SUM)

Ko dodajate čas v Excelu, morate vedeti nekaj stvari, zlasti obliko celice, ki vam bo pokazala rezultat.

Oglejmo si primer.

Spodaj imam seznam nalog skupaj s časom, ki ga bo vsako opravilo vneslo v stolpec B, in želim hitro dodati te čase in vedeti, koliko časa bo trajalo za vsa ta opravila.

V celici B9 sem uporabil preprosto formulo SUM za izračun skupnega časa, ki ga potrebujejo vse te naloge, in mi daje vrednost 18:30 (kar pomeni, da bo za dokončanje potrebnih 18 ur in 20 minut vsa ta opravila)

Zaenkrat vse dobro!

Kako sešteti več kot 24 ur v Excelu

Zdaj pa poglejte, kaj se zgodi, ko spremenim čas, ko bo naloga 2 trajala od 1 ure do 10 ur.

Rezultat zdaj pravi 03:20, kar pomeni, da bi za dokončanje vseh teh nalog potrebovali 3 ure in 20 minut.

To je napačno (očitno)

Težava tukaj ni v tem, da Excel zamoti. Težava je v tem, da je celica oblikovana tako, da vam bo prikazala le časovni del nastale vrednosti.

Ker je posledična vrednost tukaj več kot 24 ur, se je Excel odločil pretvoriti 24 -urni del v dan in ga odstraniti iz vrednosti, ki je prikazana uporabniku, ter prikazati le preostale ure, minute in sekunde.

Na srečo je to enostavno popraviti.

Vse, kar morate storiti, je, da spremenite obliko celice, da bo prikazala ure, tudi če presega 24 ur.

Spodaj je nekaj formatov, ki jih lahko uporabite:

Obliko lahko spremenite tako, da odprete pogovorno okno celice za obliko in uporabite obliko po meri ali uporabite funkcijo TEXT in uporabite katero koli od zgornjih oblik v sami formuli

Za prikaz časa lahko uporabite spodnjo formulo BESEDILO, tudi če je več kot 24 ur:

= BESEDILO (SUM (B2: B7), "[h]: mm: ss")

ali spodnjo formulo, če želite ure, ki presegajo 24 ur, pretvoriti v dneve:

= BESEDILO (SUM (B2: B7), "dd hh: mm: ss")

Rezultati, ki prikazujejo zgoščevanje (###) namesto datuma/časa (razlogi + popravljanje)

V nekaterih primerih boste morda ugotovili, da Excel namesto prikaza časovne vrednosti prikaže celice v celici.

Tu je nekaj možnih razlogov in načinov, kako jih odpraviti:

Stolpec ni dovolj širok

Če celica nima dovolj prostora za prikaz celotnega datuma, lahko prikaže simbole razpršitve.

Ima enostavno rešitev - spremenite širino stolpca in ga razširite.

Negativna vrednost datuma

Vrednost datuma ali časa ne more biti negativna v Excelu. v primeru, da izračunate časovno razliko in se izkaže, da je negativna, vam bo Excel prikazal razpršene simbole.

Način popravkov za spremembo formule, da dobite pravi rezultat. Na primer, če izračunate časovno razliko med dvema časoma in spremembo datuma, morate prilagoditi formulo, da jo upoštevate.

V drugih primerih lahko uporabite funkcijo ABS za pretvorbo negativne časovne vrednosti v pozitivno število, da se pravilno prikaže. Lahko pa uporabite tudi formulo IF, da preverite, ali je rezultat negativna vrednost, in vrnete nekaj bolj smiselnega.

V tej vadnici sem obravnaval teme o izračunu časa v Excelu (kjer lahko izračunate časovno razliko, dodate ali odštejete čas, prikažete čas v različnih oblikah in seštevate časovne vrednosti)

Upam, da vam je bila ta vadnica koristna.