Beregn tid addisjon og subtraksjon (tidsforskjell, arbeidstid) i Excel

på grunn avDatoer og klokkeslett lagres som tall i Excels backend, slik at du enkelt kan bruke enkle aritmetiske operasjoner og formler på dato- og klokkeslettverdier.

Du kan for eksempel legge til og trekke fra to forskjellige tidsverdier eller datoverdier, eller du kan beregne tidsforskjellen mellom to gitte datoer/klokkeslett.

I denne opplæringen vil jeg vise deg hvordan Utfør beregninger ved hjelp av tid i Excelflere metoder (som å beregne tidsforskjell, legge til og trekke fra tid, vise tid i forskjellige formater og beregne summen av tidsverdier).

Hvordan håndterer Excel datoer og klokkeslett?

Som jeg nevnte lagres datoer og klokkeslett som tall i celler i Excel.Heltall representerer hele dagen, brøkdelen av tallet representerer delen av dagen (kan konverteres til time-, minutt- og sekundverdier)

For eksempel representerer en verdi på 1 1900. januar 1 i Excel, som er utgangspunktet for Excel for å begynne å vurdere datoer.

Så 2 betyr 1990. januar 1, 2 betyr 3. januar 1900, og så videre, 1 betyr 3. januar 44197.

Merk: Excel for Windows og Excel for Mac følger forskjellige startdatoer.1 i Excel for Windows betyr 1900. januar 1, 1 i Excel for Mac betyr 1. januar 1904

Hvis disse tallene har noen sifre etter desimaltegnet, behandler Excel disse tallene som en del av en dag og kan konvertere dem til timer, minutter og sekunder.

For eksempel betyr 44197.5 2021. januar 1 kl. 1:12.

Så hvis du jobber med tidsverdier i Excel, jobber du i utgangspunktet med brøkdelen av tallet.

Excel gir deg fleksibiliteten til å konvertere brøkdeler til forskjellige formater, for eksempel kun timer, kun minutter, kun sekunder eller en kombinasjon av timer, minutter og sekunder

Nå som du forstår hvordan tid lagres i Excel, la oss se på noen eksempler på hvordan du kan beregne tidsforskjellen mellom to forskjellige datoer eller klokkeslett i Excel

Formelen for å beregne tidsforskjellen mellom to tider

I mange tilfeller er alt du trenger å gjøre å finne den totale medgåtte tiden mellom to tidsverdier (for eksempel i tilfelle av en timeplan med In-time og Out-time).

Metoden du velger avhenger av hvordan tiden er nevnt i cellen og hvordan du vil at resultatet skal formateres.

La oss se på noen få eksempler

Enkel subtraksjon for å beregne tidsforskjell i Excel

Siden tider lagres som tall i Excel, for å finne forskjellen mellom 2 tidsverdier, kan du enkelt trekke starttiden fra slutttiden.

sluttid - starttid

Resultatet av subtraksjonen vil også være en desimalverdi som representerer tiden som har gått mellom de to tidsverdiene.

Nedenfor er et eksempel hvor jeg har starttid og sluttid og jeg har regnet ut tidsforskjellen ved enkel subtraksjon.

Det er mulig at resultatene dine vises i tidsformat (i stedet for desimalverdier eller time/minutt-verdier).I eksemplet ovenfor viser resultatet i celle C2 09:30 i stedet for 9.5.

Dette er greit fordi Excel vil prøve å kopiere formatering fra tilstøtende kolonner.

For å konvertere den til en desimal, endre formatet på cellen til "Generelt" (dette alternativet er i fanen "Hjem" i "Tall"-gruppen)

Etter at du har fått resultatet, kan du formatere det på forskjellige måter.For eksempel kan du vise verdier i bare timer, kun minutter eller en kombinasjon av timer, minutter og sekunder.

Her er de forskjellige formatene du kan bruke:

format	hva den kan gjøre
hh	Vis kun antall timer som har gått mellom to datoer
mm	Vis timer som to sifre (f.eks. 04 eller 12)
tt: mm	Viser timer og minutter som har gått mellom to datoer, for eksempel 10:20
tt: mm: ss	Viser timer, minutter og sekunder som har gått mellom to datoer, f.eks. 10:20:36

Hvis du lurer på hvor og hvordan du skal bruke disse egendefinerte datoformatene, følg disse trinnene:

1. Velg cellene du vil bruke datoformatet på
2. Hold Ctrl og trykk 1 (Kommando + 1 hvis du bruker en Mac)
3. I dialogboksen Formater celler som åpnes, klikker du på Tall-fanen (hvis den ikke allerede er valgt)
4. Klikk på Tilpass i venstre rute
5. Skriv inn ønsket formatkode i Type-feltet (jeg bruker tt:mm:ss i dette eksemplet)
6. Klikk OK

Trinnene ovenfor vil endre formatet og vise deg verdien i henhold til formatet.

Merk at egendefinert tallformatering ikke endrer verdien i cellen.Det endrer bare hvordan verdien vises.Så jeg kan velge å kun vise timeverdien i cellen og den har fortsatt den opprinnelige verdien.

PROTIPS: Hvis de totale timene overstiger 24 timer, bruk følgende egendefinerte tallformat i stedet: [hh]:mm:ss

Beregn tidsforskjell i timer, minutter eller sekunder

Når du trekker fra tidsverdier, returnerer Excel et desimaltall som representerer den resulterende tidsforskjellen.

Siden hvert helt tall representerer en dag, vil brøkdelen av tallet representere det meste av dagen og kan enkelt konverteres til timer eller minutter eller sekunder.

Beregn tidsforskjell i timer

Anta at du har et datasett som vist nedenfor og du vil beregne antall timer mellom to tidsverdier

Formelen nedenfor vil gi deg tidsforskjellen i timer:

=(B2-A2)*24

Formelen ovenfor vil gi deg det totale antallet timer som har gått mellom to tidsverdier.

Noen ganger vil Excel prøve å hjelpe og vil også gi deg resultatene i et tidsformat (vist nedenfor).

Du kan enkelt konvertere det til tallformat ved å klikke på Hjem-fanen og velge Number som format i Number-gruppen.

Hvis du bare vil returnere det totale antallet timer som har gått mellom to ganger (uten noen brøkdel), bruk denne formelen:

=INT((B2-A2)*24)

Merk: Denne formelen fungerer bare hvis de to tidsverdiene er på samme dag.Denne formelen vil gi feil resultater hvis datoene endres (en av tidsverdiene er en annen dato, den andre er en annen dato).Når datoen endres senere i denne opplæringen, sjekk ut delen der jeg introduserer formelen for å beregne tidsforskjellen.

Beregn tidsforskjell i minutter

For å beregne tidsforskjellen i minutter, må du multiplisere den resulterende verdien med det totale antallet minutter på en dag (dvs. 1440 eller 24*60).

Anta at du har et datasett som vist nedenfor, og du vil beregne det totale antallet minutter som har gått mellom startdatoen og sluttdatoen.

Her er formelen som kan gjøre dette:

=(B2-A2)*24*60

Beregn tidsforskjell i sekunder

For å beregne tidsforskjellen i sekunder, må du multiplisere den resulterende verdien med det totale antallet sekunder på en dag (dvs. 24*60*60 eller 86400).

Anta at du har et datasett som vist nedenfor, og du vil telle det totale antallet sekunder som har gått mellom startdatoen og sluttdatoen.

Her er formelen som kan gjøre dette:

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

Beregn tidsforskjellen ved å bruke TEXT-funksjonen

En annen enkel måte å raskt få tidsforskjellen uten å bekymre deg for å endre formatet er å bruke TEKST-funksjonen.

TEKST-funksjonen lar deg spesifisere formatering i formler.

=TEKST(sluttdato-startdato, format)

Den første parameteren er beregningen du vil utføre, og den andre parameteren er formatet du vil vise resultatene av beregningen i.

Anta at du har et datasett som vist nedenfor og du vil beregne tidsforskjellen mellom to tider.

Her er noen formler som vil gi deg resultatene i forskjellige formater

Vis kun åpningstider:

=TEKST(B2-A2,"hh")

Formelen ovenfor vil kun gi deg resultatet av antall timer som har gått mellom to tidsverdier.Hvis resultatet er 9 timer og 30 minutter, vil det fortsatt bare vise 9.

Vis totalt antall minutter

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

Vis totalt antall sekunder

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

Vis timer og minutter

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

Vis timer, minutter og sekunder

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

Hvis du lurer på hva som er forskjellen mellom tt og [tt] i formatet (eller mm og [mm]), når du bruker firkantede parenteser gir det deg det totale antallet timer mellom de to datoene, selv om timeverdiene større enn 24.Så hvis du trekker fra to datoverdier som avviker med mer enn 24 timer, vil bruk av [hh] gi deg det totale antallet timer, mens hh bare vil gi deg antall timer som har gått på sluttdatoen.

Få tidsforskjellen i én enhet (timer/minutter), ignorer andre

Hvis du bare vil beregne tidsforskjellen mellom to tidsverdier i timer eller minutter eller sekunder, kan du bruke de dedikerte funksjonene HOUR, MINUTE eller SECOND.

Hver av disse funksjonene tar én parameter, tidsverdien, og returnerer den angitte tidsenheten.

Anta at du har et datasett som vist nedenfor og du vil beregne det totale antallet timer og sekunder som har gått mellom disse to gangene.

Her er formelen for å gjøre dette:

Beregn antall timer som har gått mellom to ganger

=TIME(B2-A2)

Beregn minutter basert på tidsverdiresultat (ekskludert fullførte timer)

=MINUTT(B2-A2)

Beregn sekunder fra tidsverdiresultatet (ekskludert fullførte timer og minutter)

=SEKUND(B2-A2)

Et par ting å vite når du bruker disse formlene for timer, minutter og andre:

• Forskjellen mellom sluttid og starttid kan ikke være negativ (dette er vanligvis tilfellet når datoen endres).I dette tilfellet vil disse formlene returnere #NUM!Feil
• Disse formlene bruker bare tidsdelen (og ignorerer dagdelen) av den resulterende tidsverdien.Så hvis forskjellen mellom sluttid og starttid er 2 dager, 10 timer, 32 minutter og 44 sekunder, vil HOUR-formelen gi 10, MINUTE-formelen vil gi 32, og den ANDRE formelen vil gi 44

Beregn tiden som har gått nå (fra starttidspunktet)

Hvis du ønsker å beregne totalt medgått tid mellom starttidspunkt og gjeldende tid, kan du bruke NÅ-formelen i stedet for sluttid.

NÅ-funksjonen returnerer gjeldende dato og klokkeslett i cellen den brukes i.Det er en av funksjonene som ikke godtar noen input-argumenter.

Så hvis du vil beregne den totale tiden mellom starttidspunktet og gjeldende tidspunkt, kan du bruke følgende formel:

=NÅ() - starttid

Nedenfor er et eksempel hvor jeg har starttiden i kolonne A og tiden så langt i kolonne B.

Hvis tidsforskjellen mellom startdato og -klokkeslett og gjeldende klokkeslett er mer enn 24 timer, kan du formatere resultatet for å vise dato- og tidsdelen.

Du kan gjøre dette med følgende TEKST-formel:

=TEKST(NÅ()-A2,"dd tt:ss:mm")

Du kan også oppnå det samme ved å endre cellens egendefinerte format (som beskrevet tidligere i denne opplæringen) slik at den viser dato- og klokkeslettdelen.

Hvis starttiden din bare har tidsdelen, behandler Excel den som 1990. januar 1.

I dette tilfellet, hvis du bruker NÅ-funksjonen til å beregne medgått tid til nå, vil det gi deg feil resultat (fordi den resulterende verdien også vil inneholde totalt antall dager som har gått siden 1990. januar 1).

I dette tilfellet kan du bruke følgende formel:

=NÅ()-INT(NÅ())-A2

Formelen ovenfor bruker INT-funksjonen til å fjerne dagersdelen fra verdien returnert av nå-funksjonen, som deretter brukes til å beregne tidsforskjellen.

Merk at NOW er en flyktig funksjon som oppdateres når arket endres, men ikke i sanntid

Beregn tid når datoen endres (beregn og vis negativ tid i Excel)

Metodene beskrevet så langt fungerer bra hvis slutttiden er senere enn starttiden.

Problemet oppstår imidlertid når slutttiden er lavere enn starttiden.Dette skjer vanligvis når du fyller ut en timeliste og du bare legger inn klokkeslettet og ikke hele datoen og klokkeslettet.

I dette tilfellet, hvis du jobber nattskift og datoen endres, kan slutttiden være tidligere enn starttiden.

For eksempel hvis du begynner å jobbe klokken 6 og avslutter arbeidet og pause klokken 00.

Hvis du bare brukte tidsverdier, ville det å trekke starttiden fra slutttiden gi en negativ verdi på 9 timer (9 – 18).

Excel kan ikke håndtere negative tidsverdier (og for den saks skyld mennesker, med mindre du kan reise i tid)

I dette tilfellet trenger du en måte å finne ut at datoen er endret på, og bør beregne deretter.

Heldigvis finnes det en veldig enkel løsning.

Anta at du har et datasett som nedenfor, hvor jeg har starttid og sluttid.

Som du vil legge merke til, noen ganger er starttiden om kvelden og slutttiden er om morgenen (som indikerer at det var en nattskift og at dagen hadde endret seg).

Hvis jeg bruker formelen nedenfor for å beregne tidsforskjellen, viser den et pund-tegn (uthevet i gult på bildet under) i cellene der resultatet er en negativ verdi.

= B2-A2

Dette er en HVIS-formel, hvis tidsforskjellsverdien er negativ, hvis den er negativ, returnerer den riktig resultat

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

Selv om dette fungerer bra i de fleste tilfeller, er det fortsatt ikke tilstrekkelig hvis start- og sluttid er mer enn 24 timer fra hverandre.For eksempel logger noen på kl. 1 på dag 9 og logger ut kl. 00 på dag 2.

Siden dette er mer enn 24 timer, er det ingen måte å vite om personen slutter etter 2 timer eller 26 timer.

Selv om den beste måten å fikse dette på er å sørge for at oppføringen inkluderer dato og klokkeslett, hvis det bare er tidspunktet du jobber med, bør formelen ovenfor gjøre det meste av problemet (med tanke på at det er usannsynlig at noen vil jobbe mer enn 24 timer)

Legg til og trekk fra tid i Excel

Så langt har vi sett eksempler på start- og sluttider, vi må finne tidsforskjellen.

Excel lar deg også enkelt legge til eller trekke faste tidsverdier fra eksisterende dato- og tidsverdier.

Anta for eksempel at du har en liste over oppgaver i kø, der hver oppgave tar en spesifisert tid, og du vil vite når hver oppgave avsluttes.

I dette tilfellet kan du enkelt legge til tiden hver oppgave vil ta til starttiden for å se når oppgaven forventes å være fullført.

Siden Excel lagrer dato- og tidsverdier som tall, må du sørge for at tiden du prøver å legge til er i et format som Excel allerede følger.

Hvis du for eksempel legger til 1 til en dato i Excel, vil den gi deg neste dato.Dette er fordi 1 representerer en hel dag (tilsvarer 24 timer) i Excel.

Så hvis du vil legge til 1 time til en eksisterende tidsverdi, kan du ikke gå videre og bare legge til 1 til den.Du må sørge for å konvertere timeverdien til en brøkdel som representerer en time.Det samme gjelder for å legge til minutter og sekunder.

Bruk tidsfunksjonen

Tidsfunksjonene i Excel tar time-, minutt- og sekundverdier og konverterer dem til et desimaltall som representerer tiden.

For eksempel, hvis jeg ønsker å legge til 4 timer til den eksisterende tiden, kan jeg bruke følgende formel:

= starttid + TID(4,0,0)

Dette er nyttig hvis du vet timene, minuttene og sekundene som skal legges til en eksisterende tid, og bare kan bruke TID-funksjonen uten å bekymre deg for å konvertere tiden til en desimalverdi riktig.

Vær også oppmerksom på at TID-funksjonen bare tar hensyn til heltallsdelen av time-, minutt- og sekundverdiene du angir.For eksempel, hvis jeg bruker 5.5 timer i TID-funksjonen, legger den bare til 5 timer og ignorerer brøkdelen.

Vær også oppmerksom på at TIME-funksjonen bare kan legge til verdier som er mindre enn 24 timer gamle.Dette vil gi deg et feil resultat hvis timeverdien din overstiger 24.

Det samme gjelder for minutter og sekunder, funksjonen vil kun vurdere verdier mindre enn 60 minutter og 60 sekunder

Akkurat som jeg brukte TID-funksjonen for å legge til tid, kan du også trekke fra tid.Bare endre +-tegnet i formelen ovenfor til et minustegn

Bruk grunnleggende aritmetikk

Mens tidsfunksjonen er enkel å bruke, har den noen begrensninger (som nevnt ovenfor).

Hvis du vil ha mer kontroll, kan du bruke den aritmetiske metoden jeg skal dekke her.

Konseptet er enkelt - konverter en tidsverdi til en desimalverdi som representerer delen av dagen, som du deretter kan legge til en hvilken som helst tidsverdi i Excel.

Hvis du for eksempel ønsker å legge til 24 timer til en eksisterende tidsverdi, kan du bruke følgende formel:

= starttid + 24/24

Det betyr bare at jeg legger til en dag til den eksisterende tidsverdien.

Når du nå tar det samme konseptet, la oss si at du vil legge til 30 timer til tidsverdien, kan du bruke følgende formel:

= starttid + 30/24

Formelen ovenfor gjør det samme, der heltallsdelen av (30/24) representerer det totale antallet dager av tiden du vil legge til og brøkdelen representerer timer/minutter/sekunder

På samme måte, hvis du vil at et spesifikt antall minutter skal legges til tidsverdien, kan du bruke følgende formel:

=Starttid +(Legg til minutter)/24*60

Hvis du har sekunder å legge til, kan du bruke denne formelen:

= starttid + (ekstra minutter)/24*60*60

Selv om denne metoden ikke er så enkel som å bruke tidsfunksjonen, synes jeg den er mye bedre fordi den fungerer i alle tilfeller og følger de samme konseptene.I motsetning til tidsfunksjonen, trenger du ikke å bekymre deg for å legge til tid mindre enn eller mer enn 24 timer

Du kan også følge det samme konseptet mens du trekker fra tid.Bare endre + til negativt fortegn i formelen ovenfor

Hvordan summere tid i Excel

Noen ganger vil du kanskje raskt legge til alle tidsverdier i Excel.Det er enkelt å legge til flere tidsverdier i Excel (bare en enkel SUM-formel)

Men når du legger til tid i Excel, må du vite et par ting, spesielt celleformatet som viser deg resultatene.

La oss se på et eksempel.

Nedenfor har jeg en liste over oppgaver i kolonne B og tiden hver oppgave vil ta, og jeg vil raskt legge til disse tidspunktene og vite den totale tiden alle disse oppgavene vil ta.

I celle B9 brukte jeg en enkel SUM-formel for å beregne den totale tiden alle disse oppgavene ville ta, og den ga meg en verdi på 18:30 (som betyr at det ville ta 18 timer og 20 minutter å fullføre alle disse oppgavene)

Så langt så bra!

Slik summerer du over 24 timer i Excel

Se nå hva som skjer når jeg endrer fullføringstiden for oppgave 2 fra 1 time til 10 timer.

Resultatet viser nå 03:20, som betyr at det vil ta 3 timer og 20 minutter å fullføre alle disse oppgavene.

Dette er feil (åpenbart)

Problemet her er ikke at Excel er ødelagt.Problemet her er at cellene er formatert til å vise bare tidsdelen av den resulterende verdien.

Og siden den resulterende verdien her overstiger 24 timer, bestemmer Excel seg for å konvertere 24-timersdelen til en dag, fjerne den fra verdien som vises for brukeren, og bare vise de gjenværende timene, minuttene og sekundene.

Heldigvis er dette enkelt å fikse.

Alt du trenger å gjøre er å endre celleformatet for å tvinge det til å vise timene selv om det er over 24 timer.

Her er noen formater du kan bruke:

format	forventet resultat
[H]: mm	28:30
[m]:ss	1710:00
d "D" tt:mm	Dag 04 30:XNUMX
d "D" hh "Min" ss "Sek"	1 dag 04 minutter 00 sekunder
d "Dag" hh "Minutt" ss "Sekunder"	1 dag 04 minutter 00 sekunder

Du kan endre formatet ved å gå til dialogboksen Formater celler og bruke et tilpasset format, eller bruke TEKST-funksjonen og bruke et av formatene ovenfor i selve formelen

Du kan bruke følgende TEKST-formel for å vise tiden selv om den overskrider 24 timer:

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

Eller hvis du vil konvertere timer over 24 timer til dager, bruk følgende formel:

=TEKST(SUM(B2:B7),"dd tt:mm:ss")

Resultatene viser hash (###) i stedet for dato/klokkeslett (årsak + fiks)

I noen tilfeller kan du oppleve at i stedet for å vise tidsverdier, viser Excel pundtegn i cellene.

Her er noen mulige årsaker og hvordan du kan fikse dem:

Søylene er ikke brede nok

Når en celle ikke har nok plass til å vise hele datoen, kan den vise et pund-tegn.

Den har en enkel løsning - endre kolonnebredden og gjør den bredere.

negativ datoverdi

Dato- eller tidsverdier i Excel kan ikke være negative.Hvis du beregner tidsforskjellen og resultatet er negativt, vil Excel vise hash-tegnet.

Fix metode for å endre formel for å gi riktig resultat.Hvis du for eksempel beregner tidsforskjellen mellom to tider, og datoen endres, må du justere formelen for å ta hensyn til det.

I andre tilfeller kan du bruke ABS-funksjonen til å konvertere negative tidsverdier til positive tall for riktig visning.Alternativt kan du bruke en HVIS-formel for å sjekke om resultatet er negativt og returnere en mer meningsfull verdi.

I denne opplæringen dekket jeg emner om å beregne tid i Excel (hvor du kan beregne tidsforskjeller, legge til og trekke fra tid, vise tid i forskjellige formater og summere tidsverdier)

Jeg håper du fant denne opplæringen nyttig.

Ofte stilte spørsmål om beregning av tid addisjon og subtraksjon i Excel