Izračunajte seštevanje in odštevanje časa (časovna razlika, delovni čas) v Excelu

zaradiDatumi in ure so shranjeni kot številke v zaledju Excela, tako da lahko enostavno uporabljate preproste aritmetične operacije in formule na datumskih in časovnih vrednostih.

Na primer, lahko dodate in odštejete dve različni časovni vrednosti ali vrednosti datuma ali pa lahko izračunate časovno razliko med dvema danima datumoma/časoma.

V tej vadnici vam bom pokazal, kako Izvedite izračune s časom v Exceluveč metod (kot je izračun časovne razlike, seštevanje in odštevanje časa, prikaz časa v različnih oblikah in izračun vsote časovnih vrednosti).

Kako Excel obravnava datume in ure?

Kot sem že omenil, so datumi in časi shranjeni kot številke v celicah v Excelu.Celo število predstavlja cel dan, delni del števila predstavlja del dneva (lahko ga pretvorimo v vrednosti ur, minut in sekund)

Vrednost 1 na primer predstavlja 1900. januar 1 v Excelu, kar je izhodišče, da Excel začne upoštevati datume.

Torej 2 pomeni 1990. januar 1, 2 pomeni 3. januar 1900 in tako naprej, 1 pomeni 3. januar 44197.

Opomba: Excel za Windows in Excel za Mac sledita različnim začetnim datumom.1 v Excelu za Windows pomeni 1900. januar 1, 1 v Excelu za Mac pomeni 1. januar 1904

Če imajo te številke katero koli števko za decimalno vejico, Excel te številke obravnava kot del dneva in jih lahko pretvori v ure, minute in sekunde.

Na primer, 44197.5 pomeni 2021. januar 1 ob 1.

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

Excel vam omogoča fleksibilnost za pretvorbo ulomnih delov v različne oblike, kot so samo ure, samo minute, samo sekunde ali kombinacija ur, minut in sekund

Zdaj, ko razumete, kako je čas shranjen v Excelu, si oglejmo nekaj primerov, kako izračunati časovno razliko med dvema različnima datumoma ali časoma v Excelu.

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

V mnogih primerih je vse, kar morate storiti, ugotoviti skupni pretečeni čas med dvema časovnima vrednostima (na primer v primeru urnika s časom In-time in Out-time).

Metoda, ki jo izberete, je odvisna od tega, kako je čas omenjen v celici in kako želite, da je rezultat oblikovan.

Poglejmo si nekaj primerov

Preprosto odštevanje za izračun časovne razlike v Excelu

Ker so časi v Excelu shranjeni kot številke, lahko za iskanje razlike med dvema časovnima vrednostma enostavno odštejete začetni čas od končnega časa.

končni čas - čas začetka

Rezultat odštevanja bo tudi decimalna vrednost, ki predstavlja pretečeni čas med obema časovnima vrednostma.

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

Možno je, da so vaši rezultati prikazani v časovnem formatu (namesto decimalnih ali ur/minut).V zgornjem primeru rezultat v celici C2 prikazuje 09:30 namesto 9.5.

To je v redu, ker bo Excel poskušal kopirati oblikovanje iz sosednjih stolpcev.

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

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

Tu so različne oblike, ki jih lahko uporabite:

format	kaj lahko naredi
hh	Pokaži samo število ur, ki so pretekle med dvema datumoma
mm	Prikaz ur kot dve števki (npr. 04 ali 12)
hh: mm	Prikazuje ure in minute, ki so pretekle med dvema datumoma, npr. 10:20
hh: mm: ss	Prikaže ure, minute in sekunde, ki so pretekle med dvema datumoma, npr. 10:20:36

Če se sprašujete, kje in kako uporabiti te oblike datumov po meri, sledite tem korakom:

1. Izberite celice, za katere želite uporabiti obliko datuma
2. Pridržite Control in pritisnite 1 (Command + 1, če uporabljate Mac)
3. V pogovornem oknu Oblikovanje celic, ki se odpre, kliknite zavihek Številke (če še ni izbran)
4. V levem podoknu kliknite Prilagodi
5. V polje Vrsta vnesite poljubno kodo želene oblike (v tem primeru uporabljam hh:mm:ss)
6. Kliknite V redu

Zgornji koraki bodo spremenili obliko in vam pokazali vrednost glede na obliko.

Upoštevajte, da oblikovanje številk po meri ne spremeni vrednosti v celici.Samo spremeni način prikaza vrednosti.Tako se lahko odločim, da v celici prikažem samo vrednost ure in ima še vedno prvotno vrednost.

NASVET: Če skupno število ur presega 24 ur, namesto tega uporabite naslednjo obliko številk 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 dan, bo delni del števila predstavljal večino dneva in ga je mogoče enostavno pretvoriti v ure, minute ali sekunde.

Izračunajte časovno razliko v urah

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

Spodnja formula vam bo dala časovno razliko v urah:

= (B2-A2) * 24

Zgornja formula vam bo dala skupno število ur, ki pretečejo med dvema časovnima vrednostma.

Včasih vam bo Excel poskušal pomagati in vam bo dal tudi rezultate v časovni obliki (prikazano spodaj).

Preprosto ga lahko pretvorite v obliko številk, tako da kliknete zavihek Domov in izberete Številka kot obliko zapisa v skupini Število.

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

=INT((B2-A2)*24)

Opomba: ta formula deluje samo, če sta obe časovni vrednosti na isti dan.Ta formula bo dala napačne rezultate, če se datumi spremenijo (ena od časovnih vrednosti je drug datum, druga pa drug datum).Ko se datum spremeni pozneje v tej vadnici, si oglejte razdelek, kjer predstavljam formulo za izračun časovne razlike.

Izračunajte časovno razliko v minutah

Za izračun časovne razlike v minutah morate dobljeno vrednost pomnožiti s skupnim številom minut na dan (tj. 1440 ali 24*60).

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

Tukaj je formula, ki lahko to naredi:

=(B2-A2)*24*60

Izračunajte časovno razliko v sekundah

Za izračun časovne razlike v sekundah morate dobljeno vrednost pomnožiti s skupnim številom sekund v dnevu (tj. 24*60*60 ali 86400).

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

Tukaj je formula, ki lahko to naredi:

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

Izračunajte časovno razliko s funkcijo TEXT

Drug preprost način za hitro pridobivanje časovne razlike brez skrbi glede spreminjanja formata je uporaba funkcije BESEDILO.

Funkcija TEXT vam omogoča, da določite oblikovanje v formulah.

=TEXT(končni_datum-začetni_datum, oblika)

Prvi parameter je izračun, ki ga želite izvesti, drugi parameter pa je oblika, v kateri želite prikazati rezultate izračuna.

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

Tukaj je nekaj formul, ki vam bodo dale rezultate v različnih oblikah

Pokaži samo ure:

=BESEDILO(B2-A2,"hh")

Zgornja formula vam bo dala samo rezultat števila ur, ki so pretekle med dvema časovnima vrednostma.Če je vaš rezultat 9 ur 30 minut, bo še vedno prikazan samo 9.

Prikaži skupno število minut

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

Pokaži skupno število sekund

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

Prikaz ur in minut

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

Prikaz ur, minut in sekund

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

Če se sprašujete, kakšna je razlika med hh in [hh] v formatu (ali mm in [mm]), vam pri uporabi oglatih oklepajev podate skupno število ur med obema datumoma, tudi če je ura Vrednosti več kot 24.Če torej odštejete dve vrednosti datuma, ki se razlikujeta za več kot 24 ur, vam bo uporaba [hh] dala skupno število ur, medtem ko vam bo hh dalo samo število ur, ki so pretekle na končni datum.

Dobite časovno razliko v eni enoti (ure/minute), pri čemer druge ne upoštevate

Če želite samo izračunati časovno razliko med dvema časovnima vrednostma v urah, minutah ali sekundah, lahko uporabite namenske funkcije HOUR, MINUTE ali SECOND.

Vsaka od teh funkcij vzame en parameter, časovno vrednost, in vrne določeno časovno enoto.

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

Tukaj je formula za to:

Izračunajte število ur, ki pretečejo med dvema časoma

=URA(B2-A2)

Izračunajte minute na podlagi rezultata časovne vrednosti (brez dokončanih ur)

=MINUTA(B2-A2)

Izračunajte sekunde iz rezultata časovne vrednosti (brez dokončanih ur in minut)

=DRUGA(B2-A2)

Nekaj ​​stvari, ki jih morate vedeti, ko uporabljate te formule HOUR, MINUTE in SECOND:

• Razlika med končnim in začetnim časom ne more biti negativna (to se običajno zgodi, ko se datum spremeni).V tem primeru bodo te formule vrnile #NUM!napaka
• Te formule uporabljajo samo časovni del (in prezrejo del dni) nastale časovne vrednosti.Torej, če je razlika med končnim in začetnim časom 2 dni, 10 ur, 32 minut in 44 sekund, bo formula HOUR dala 10, formula MINUTE bo dala 32, formula DRUGA pa 44

Izračunajte čas, ki je potekel zdaj (od začetka)

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

Funkcija NOW vrne trenutni datum in čas v celici, v kateri se uporablja.To je ena od funkcij, ki ne sprejema nobenih vhodnih argumentov.

Torej, če želite izračunati skupni pretečeni čas med začetnim in trenutnim časom, lahko uporabite naslednjo formulo:

=NOW() - začetni čas

Spodaj je primer, kjer imam začetni čas v stolpcu A in dosedanji čas v stolpcu B.

Če je časovna razlika med začetnim datumom in uro ter trenutnim časom večja od 24 ur, lahko rezultat formatirate tako, da prikažete del datuma in časa.

To lahko storite z naslednjo formulo TEXT:

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

Enako lahko dosežete tudi tako, da spremenite obliko celice po meri (kot je opisano prej v tej vadnici), tako da prikazuje del datuma in časa.

Če ima vaš začetni čas samo časovni del, ga Excel obravnava kot 1990. januar 1.

V tem primeru, če uporabite funkcijo ZDAJ za izračun pretečenega časa do zdaj, vam bo dala napačen rezultat (ker bo nastala vrednost vsebovala tudi skupno število dni, ki so pretekli od 1990. januarja 1).

V tem primeru lahko uporabite naslednjo formulo:

=SEDAJ()-INT(SEDAJ())-A2

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

Upoštevajte, da je ZDAJ spremenljiva funkcija, ki se posodablja, ko se list spremeni, vendar ne v realnem času

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

Do sedaj opisane metode dobro delujejo, če je vaš končni čas kasnejši od začetnega.

Vendar se težava pojavi, ko je vaš končni čas krajši od začetnega.To se običajno zgodi, ko izpolnite časovni list in vnesete samo čas in ne celotnega datuma in ure.

V tem primeru, če delate v nočni izmeni in se datum spremeni, je lahko vaš končni čas prej kot začetni.

Na primer, če začnete z delom ob 6:00 in končate z delom in se ustavite ob 9:00.

Če bi uporabljali samo časovne vrednosti, bi odštevanje začetnega časa od končnega časa dalo negativno vrednost 9 ur (9 – 18).

Excel ne more obdelati negativnih časovnih vrednosti (in, če že to zadevo, ljudi, razen če lahko potujete v času)

V tem primeru potrebujete način, da ugotovite, ali se je datum spremenil, in ga morate ustrezno izračunati.

Na srečo obstaja zelo preprosta rešitev.

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

Kot boste opazili, je včasih začetni čas zvečer, končni čas pa zjutraj (kar pomeni, da je bila izmena čez noč in se je dan spremenil).

Če za izračun časovne razlike uporabim spodnjo formulo, prikaže znak funta (označen rumeno na spodnji sliki) v celicah, kjer je rezultat negativna vrednost.

= B2-A2

To je formula IF, če je vrednost časovne razlike negativna, če je negativna, vrne pravilen rezultat

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

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

Ker je to več kot 24 ur, ni mogoče vedeti, ali oseba preneha po 2 urah ali 26 urah.

Čeprav je najboljši način, da to odpravite, je zagotoviti, da vnos vključuje datum in uro, če je to samo čas, s katerim delate, bi morala zgornja formula narediti večino težave (glede na to, da je malo verjetno, da bo kdo delal več kot 24 ur)

Dodajanje in odštevanje časa v Excelu

Do sedaj smo videli primere začetka in konca časa, najti moramo časovno razliko.

Excel vam omogoča tudi preprosto dodajanje ali odštevanje fiksnih časovnih vrednosti od obstoječih vrednosti datuma in časa.

Denimo, da imate na primer seznam opravil v čakalni vrsti, kjer vsako opravilo traja določen čas, in želite vedeti, kdaj se vsako opravilo konča.

V tem primeru lahko začetnemu času preprosto dodate čas, ki ga potrebuje posamezno opravilo, da vidite, kdaj naj bi bilo opravilo dokončano.

Ker Excel shranjuje vrednosti datuma in časa kot številke, se morate prepričati, da je čas, ki ga poskušate dodati, v obliki, ki ji Excel že sledi.

Na primer, če datumu v Excelu dodate 1, vam bo dal naslednji datum.To je zato, ker 1 predstavlja cel dan (kar ustreza 24 urah) v Excelu.

Če torej želite obstoječi časovni vrednosti dodati 1 uro, ne morete nadaljevati in ji preprosto dodati 1.Prepričati se morate, da ste vrednost ure pretvorili v delni del, ki predstavlja uro.Enako velja za dodajanje minut in sekund.

Uporabite funkcijo časa

Funkcije časa v Excelu vzamejo vrednosti ure, minute in sekunde in jih pretvorijo v decimalno število, ki predstavlja čas.

Na primer, če bi želel obstoječemu času dodati 4 ure, bi lahko uporabil naslednjo formulo:

= začetni čas + ČAS(4,0,0)

To je uporabno, če poznate ure, minute in sekunde, ki jih je treba dodati obstoječemu času, in lahko preprosto uporabite funkcijo ČAS, ne da bi vas skrbelo, da bi čas pravilno pretvorili v decimalno vrednost.

Upoštevajte tudi, da funkcija TIME upošteva samo celoštevilski del vrednosti ur, minut in sekund, ki jih vnesete.Na primer, če v funkciji TIME uporabim 5.5 ure, samo doda 5 ur in prezre delni del.

Upoštevajte tudi, da lahko funkcija TIME doda samo vrednosti, ki so stare manj kot 24 ur.To vam bo dalo napačen rezultat, če vaša vrednost ure preseže 24.

Enako velja za dele minut in sekund, funkcija bo upoštevala samo vrednosti, manjše od 60 minut in 60 sekund

Tako kot sem uporabil funkcijo ČAS za dodajanje časa, lahko tudi odštejete čas.Samo spremenite znak + v zgornji formuli v znak minus

Uporabite osnovno aritmetiko

Čeprav je časovna funkcija enostavna za uporabo, ima nekatere omejitve (kot je navedeno 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, ki jo lahko nato dodate kateri koli časovni vrednosti v Excelu.

Na primer, če želite obstoječi časovni vrednosti dodati 24 ur, lahko uporabite naslednjo formulo:

= čas začetka + 24/24

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

Zdaj ob istem konceptu, recimo, da želite časovni vrednosti dodati 30 ur, lahko uporabite naslednjo formulo:

= čas začetka + 30/24

Zgornja formula naredi enako, kjer celo število (30/24) predstavlja skupno število dni časa, ki ga želite dodati, in ulomni del predstavlja ure/minute/sekunde

Podobno, če želite določeno število minut dodati časovni vrednosti, lahko uporabite naslednjo formulo:

=Začetni čas +(Dodaj minute)/24*60

Če imate nekaj sekund za dodajanje, lahko uporabite to formulo:

= začetni čas + (dodatne minute)/24*60*60

Čeprav ta metoda ni tako preprosta kot uporaba časovne funkcije, se mi zdi veliko boljša, ker deluje v vseh primerih in sledi istim konceptom.Za razliko od funkcije časa vam ni treba skrbeti, da bi dodali čas, krajši ali večji od 24 ur

Enak koncept lahko sledite tudi pri odštevanju časa.Samo spremenite + v negativni predznak v zgornji formuli

Kako sešteti čas v Excelu

Včasih boste morda želeli hitro dodati vse časovne vrednosti v Excelu.Dodajanje več časovnih vrednosti v Excelu je preprosto (samo preprosta formula SUM)

Ko pa dodate čas v Excelu, morate vedeti nekaj stvari, zlasti obliko celice, ki vam bo pokazala rezultate.

Poglejmo primer.

Spodaj imam seznam nalog v stolpcu B in čas, ki ga bo zahtevalo vsako opravilo, in želim hitro dodati te čase in vedeti, koliko časa bodo vse te naloge vzele.

V celici B9 sem uporabil preprosto formulo SUM za izračun skupnega časa za vse te naloge in mi daje vrednost 18:30 (kar pomeni, da traja 18 ur in 20 minut, da dokončam vse te naloge)

Zaenkrat tako dobro!

Kako sešteti več kot 24 ur v Excelu

Zdaj pa poglejte, kaj se zgodi, ko spremenim čas dokončanja naloge 2 iz 1 ure na 10 ur.

Rezultat zdaj kaže 03:20, kar pomeni, da bo za dokončanje vseh teh nalog potrebnih 3 ure in 20 minut.

To je napačno (očitno)

Problem tukaj ni v tem, da je Excel pokvarjen.Težava je v tem, da so celice oblikovane tako, da prikažejo samo časovni del nastale vrednosti.

In ker dobljena vrednost tukaj presega 24 ur, se Excel odloči, da pretvori 24-urni del v dan, ga odstrani iz vrednosti, prikazane uporabniku, in prikaže samo preostale ure, minute in sekunde.

Na srečo je to enostavno popraviti.

Vse, kar morate storiti, je spremeniti obliko celice, da bo prikazala ure, tudi če je več kot 24 ur.

Tukaj je nekaj formatov, ki jih lahko uporabite:

format	pričakovani rezultati
[h]: mm	28:30
[m]: ss	1710:00
d "D" hh:mm	04. dan 30:XNUMX
d “D” hh “Min” ss “Sek”	1 dan 04 minute 00 sekund
d “Dan” hh “Minuta” ss “Sekunde”	1 dan 04 minute 00 sekund

Obliko lahko spremenite tako, da odprete pogovorno okno Oblika celic in uporabite obliko po meri ali uporabite funkcijo BESEDILO in uporabite katero koli od zgornjih oblik zapisa v sami formuli

Za prikaz časa lahko uporabite naslednjo formulo TEXT, tudi če presega 24 ur:

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

Če pa želite pretvoriti ure v 24 urah v dni, uporabite naslednjo formulo:

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

Rezultati prikazujejo hash (###) namesto datuma/časa (vzrok + popravek)

V nekaterih primerih boste morda ugotovili, da Excel namesto prikaza časovnih vrednosti prikaže znake funtov v celicah.

Tukaj je nekaj možnih vzrokov in kako jih odpraviti:

Stolpci niso dovolj široki

Če celica nima dovolj prostora za prikaz celotnega datuma, lahko prikaže znak funta.

To je enostavno popraviti - spremenite širino stolpca in ga povečajte.

negativna vrednost datuma

Vrednosti datuma ali časa v Excelu ne morejo biti negativne.Če računate časovno razliko in je rezultat negativen, bo Excel prikazal predznak.

Popravite metodo za spremembo formule za pravilen rezultat.Če na primer izračunate časovno razliko med dvema časoma in se datum spremeni, boste morali prilagoditi formulo, da boste to upoštevali.

V drugih primerih lahko uporabite funkcijo ABS za pretvorbo negativnih časovnih vrednosti v pozitivne številke za pravilen prikaz.Lahko pa uporabite formulo IF, da preverite, ali je rezultat negativen, in vrnete bolj smiselno vrednost.

V tej vadnici sem obravnaval teme o izračunavanju časa v Excelu (kjer lahko izračunate časovne razlike, seštevate in odštevate čas, prikazujete čas v različnih formatih in seštevate časovne vrednosti)

Upam, da se vam je ta vadnica zdela koristna.

Pogosto zastavljena vprašanja o izračunu seštevanja in odštevanja časa v Excelu