Beregn tid i Excel (tidsforskjell, arbeidstimer, legg til/ trekk fra)

Siden datoer og klokkeslett er lagret som tall i bakenden i Excel, kan du enkelt bruke enkle regneoperasjoner og formler på dato- og klokkeslettverdiene.

For eksempel kan du legge til to forskjellige tidsverdier eller datoverdier, eller du kan beregne tidsforskjellen mellom to gitte datoer/klokkeslett.

I denne opplæringen vil jeg vise deg et par måter å utføre beregninger ved hjelp av tid i Excel (for eksempel å beregne tidsforskjellen, legge til eller trekke fra tid, vise tid i forskjellige formater og gjøre en sum tidsverdier)

Hvordan håndterer Excel dato og klokkeslett?

Som jeg nevnte, lagres datoer og klokkeslett som tall i en celle i Excel. Et helt tall representerer en hel dag og desimaldelen av et tall vil representere delen av dagen (som kan konverteres til timer, minutter og sekunder)

For eksempel representerer verdien 1 1. januar 1900 i Excel, som er utgangspunktet Excel begynner å vurdere datoene fra.

Så 2 ville bety 02.01.1990, 3 ville bety 03.01.1900 og så videre, og 44197 ville bety 01.01.2021.

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

Hvis det er noen sifre etter et desimaltegn i disse tallene, vil Excel betrakte dem som en del av dagen, og det kan konverteres til timer, minutter og sekunder.

For eksempel vil 44197.5 bety 1. januar 2021 12:00:00 PM.

Så hvis du jobber med tidsverdier i Excel, vil du i hovedsak jobbe med desimaldelen av et tall.

Og Excel gir deg fleksibiliteten til å konvertere den desimale delen til forskjellige formater, for eksempel bare timer, bare minutter, bare sekunder, eller en kombinasjon av timer, minutter og sekunder

Nå som du forstår hvordan tiden lagres i Excel, la oss se på noen eksempler på hvordan du beregner tidsforskjellen mellom to forskjellige datoer eller tider i Excel

Formler for å beregne tidsforskjell mellom to ganger

I mange tilfeller er alt du vil gjøre å finne ut den totale tiden som har gått mellom to-tidsverdiene (for eksempel når det gjelder et timeliste som har In-time og Out-time).

Metoden du velger vil avhenge av hvordan tiden er nevnt i en celle og i hvilket format du vil ha resultatet.

La oss se på et par eksempler

Enkel subtraksjon av Beregn tidsforskjell i Excel

Siden tiden er lagret som et tall i Excel, finn forskjellen mellom 2 tidsverdier, kan du enkelt trekke starttiden fra sluttiden.

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 start- og sluttidspunktet, og jeg har beregnet tidsforskjellen med en enkel subtraksjon.

Det er en mulighet for at resultatene vises i tidsformatet (i stedet for desimaler eller i timer/minutter -verdier). I vårt eksempel ovenfor viser resultatet i celle C2 09:30 i stedet for 9,5.

Det er helt greit ettersom Excel prøver å kopiere formatet fra den tilstøtende kolonnen.

For å konvertere dette til en desimal, endre formatet på cellene til Generelt (alternativet er i kategorien Hjem i gruppen Numbers)

Når du har resultatet, kan du formatere det på forskjellige måter. For eksempel kan du vise verdien i bare timer eller minutter eller en kombinasjon av timer, minutter og sekunder.

Nedenfor er de forskjellige formatene du kan bruke:

Format	Hva det gjør
h	Viser bare timene som har gått mellom de to datoene
hh	Viser timer med tosiffer (for eksempel 04 eller 12)
hh: mm	Viser timer og minutter mellom de to datoene, for eksempel 10:20
hh: mm: ss	Viser timer, minutter og sekunder mellom de to datoene, for eksempel 10:20:36

Og hvis du lurer på hvor og hvordan du bruker disse egendefinerte datoformatene, følg trinnene nedenfor:

1. Velg cellene der du vil bruke datoformatet
2. Hold nede Ctrl -tasten og trykk på 1 -tasten (eller Command + 1 hvis du bruker Mac)
3. I dialogboksen Formater celler som åpnes, klikker du på kategorien Nummer (hvis den ikke allerede er valgt)
4. Klikk på Tilpasset i den venstre ruten
5. Skriv inn hvilken som helst ønsket formatkode i Type -feltet (i dette eksemplet bruker jeg hh: mm: ss)
6. Klikk OK

Trinnene ovenfor vil endre formateringen og vise deg verdien basert på formatet.

Vær oppmerksom på at tilpasset nummerformatering ikke endrer verdien i cellen. Det endrer bare måten en verdi vises på. Så jeg kan velge å bare vise timeverdien i en celle, mens den fortsatt vil ha den opprinnelige verdien.

Profftips: Hvis det totale timetallet overstiger 24 timer, bruker du i stedet følgende tilpassede tallformat: [hh]: mm: ss

Beregn tidsforskjellen i timer, minutter eller sekunder

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

Siden hvert heltall representerer en dag, vil desimaldelen av tallet representere den store delen av dagen som lett kan konverteres til timer eller minutter eller sekunder.

Beregning av tidsforskjell i timer

Anta at du har datasettet som vist nedenfor, og du vil beregne antall timer mellom to-tidsverdiene

Nedenfor er formelen som gir deg tidsforskjellen i timer:

= (B2-A2)*24

Formelen ovenfor gir deg det totale antallet timer som har gått mellom to-tidsverdiene.

Noen ganger prøver Excel å være nyttig og gir deg også resultatet i tidsformat (som vist nedenfor).

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

Hvis du bare vil returnere det totale antallet timer som har gått mellom de to gangene (uten desimaldel), bruker du formelen nedenfor:

= INT ((B2-A2)*24)

Merk: Denne formelen fungerer bare når begge tidsverdiene er av samme dag. Hvis dagen endres (hvor en av tidsverdiene er for en annen dato og den andre for en annen dato), vil denne formelen gi feil resultater. Ta en titt på delen der jeg dekker formelen for å beregne tidsforskjellen når datoen endres senere i denne opplæringen.

Beregning av tidsforskjell i minutter

For å beregne tidsforskjellen i minutter, må du multiplisere den resulterende verdien med det totale antallet minutter på en dag (som er 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 start og sluttdato.

Nedenfor er formelen som vil gjøre det:

= (B2-A2)*24*60

Beregning av tidsforskjell i sekunder

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

Anta at du har et datasett som vist nedenfor, og du vil beregne det totale antallet sekunder som har gått mellom start- og sluttdato.

Nedenfor er formelen som vil gjøre det:

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

Beregner tidsforskjell med TEKST -funksjonen

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

TEKST -funksjonen lar deg spesifisere formatet rett i formelen.

= TEKST (Sluttdato - Startdato, format)

Det første argumentet er beregningen du vil gjøre, og det andre argumentet er formatet du vil vise resultatet av beregningen i.

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

Her er noen formler som gir deg resultatet med forskjellige formater

Vis bare antall timer:

= TEKST (B2-A2, "hh")

Formelen ovenfor gir deg bare resultatet som viser antall timer som er gått mellom to-tidsverdiene. Hvis resultatet er 9 timer og 30 minutter, vil det fremdeles bare vise deg 9.

Vis antall minutter totalt

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

Vis antall totale sekunder

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

Vis timer og minutter

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

Vis timer, minutter og sekunder

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

Hvis du lurer på hva som er forskjellen mellom hh og [hh] i formatet (eller mm og [mm]), vil det gi deg totalt antall timer mellom de to datoene når du bruker firkantede parenteser, selv om timeverdien er høyere enn 24. Så hvis du trekker fra to datoverdier der forskjellen er mer enn 24 timer, vil bruk av [hh] gi deg det totale timetallet og hh vil bare gi deg timene som har gått på dagen for sluttdatoen .

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

Hvis du vil beregne tidsforskjellen mellom de to tidsverdiene på bare antall timer eller minutter eller sekunder, kan du bruke den dedikerte timen, minuttet eller sekundfunksjonen.

Hver av disse funksjonene tar ett enkelt argument, som er tidsverdien, og returnerer den angitte tidsenheten.

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

Nedenfor er formlene for å gjøre dette:

Beregnede timer som gikk mellom to ganger

= TIME (B2-A2)

Beregne minutter fra tidsverdieresultatet (eksklusive de fullførte timene)

= MINUTT (B2-A2)

Beregning av sekunder fra tidsverdieresultatet (eksklusive de fullførte timene og minuttene)

= ANDRE (B2-A2)

Et par ting du trenger å vite når du arbeider med disse timene, minuttene og andre formlene:

• Forskjellen mellom sluttid og starttid kan ikke være negativ (som ofte er tilfelle når datoen endres). I slike tilfeller vil disse formlene returnere et #NUM! feil
• Disse formlene bruker bare tidsdelen av den resulterende tidsverdien (og ignorer dagdelen). Så hvis forskjellen i 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 forløpt tid til nå (fra starttiden)

Hvis du vil beregne den totale tiden som har gått mellom starttiden og den nåværende tiden, kan du bruke NÅ -formelen i stedet for sluttiden.

NOW -funksjonen returnerer gjeldende dato og klokkeslett i cellen den brukes i. Det er en av de funksjonene som ikke tar noen inputargument.

Så hvis du vil beregne den totale tiden som har gått mellom starttiden og den nåværende tiden, kan du bruke formelen nedenfor:

= NÅ () - Starttid

Nedenfor er et eksempel der jeg har starttidene i kolonne A, og tiden som har gått til nå i kolonne B.

Hvis tidsforskjellen mellom startdato og klokkeslett og gjeldende tid er mer enn 24 timer, kan du formatere resultatet for å vise dagen så vel som tidsdelen.

Du kan gjøre det ved å bruke TEKST -formelen nedenfor:

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

Du kan også oppnå det samme ved å endre den egendefinerte formateringen av cellen (som dekket tidligere i denne opplæringen) slik at den viser dagen så vel som tidsdelen.

Hvis starttiden bare har tidsdelen, vil Excel betrakte den som tiden 1. januar 1990.

I dette tilfellet, hvis du bruker NOW -funksjonen til å beregne tiden som har gått til nå, kommer det til å gi deg feil resultat (ettersom den resulterende verdien også vil ha de totale dagene som har gått siden 1. januar 1990).

I et slikt tilfelle kan du bruke formelen nedenfor:

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

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

Vær oppmerksom på at NÅ er en flyktig funksjon som oppdateres når det er en endring i regnearket, men den oppdateres ikke i sanntid

Beregn tid når datoen endres (beregne og vis negative tider i Excel)

Metodene som er dekket så langt fungerer godt hvis sluttiden din er senere enn starttiden.

Men problemet oppstår når sluttiden din er lavere enn starttiden. Dette skjer ofte når du fyller ut timelister der du bare angir klokkeslettet og ikke hele datoen og klokkeslettet.

I slike tilfeller, hvis du jobber på en nattskift og datoen endres, er det en mulighet for at sluttiden din vil være tidligere enn starttiden.

For eksempel, hvis du starter arbeidet ditt kl. 18.00 om kvelden, og fullfører arbeidet og tidsavbruddet kl. 09.00 om morgenen.

Hvis du bare jobber med tidsverdier, vil subtrahering av starttid fra sluttiden gi deg en negativ verdi på 9 timer (9 - 18).

Og Excel kan ikke håndtere negative tidsverdier (og for den saks skyld heller ikke mennesker, med mindre du kan tidsreise)

I slike tilfeller trenger du en måte å finne ut at dagen har endret seg og beregningen bør gjøres deretter.

Heldigvis er det en veldig enkel løsning for dette.

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

Som du vil legge merke til at noen ganger er starttiden om kvelden og sluttiden er om morgenen (noe som indikerer at dette var et nattskift og dagen har endret seg).

Hvis jeg bruker formelen nedenfor for å beregne tidsforskjellen, vil det vise meg hash -tegnene i cellene der resultatet er en negativ verdi (markert med gult i bildet under).

= B2-A2

Her er en IF -formel om hvorvidt tidsdifferansen er negativ eller ikke, og hvis den er negativ, returnerer den det riktige resultatet

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

Selv om dette fungerer bra i de fleste tilfeller, kommer det fremdeles til kort i tilfelle starttid og sluttid er mer enn 24 timer fra hverandre. For eksempel logger noen seg inn kl. 09.00 på dag 1 og logger av kl. 11.00 på dag 2.

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

Selv om den beste måten å takle dette på er å sørge for at oppføringene inkluderer dato så vel som klokkeslett, men hvis det bare er tiden du jobber med, bør formelen ovenfor ta seg av de fleste problemene ( med tanke på at det er usannsynlig for noen å jobbe i mer enn 24 timer)

Legge til/ trekke fra tid i Excel

Så langt har vi sett eksempler der vi hadde start- og sluttidspunktet, og vi trengte å finne tidsforskjellen.

Excel lar deg også enkelt legge til eller trekke fra en fast tidsverdi fra den eksisterende dato- og klokkeslettet.

La oss for eksempel si at du har en liste over oppgaver i kø hvor hver oppgave tar den angitte tiden, og du vil vite når hver oppgave vil ende.

I et slikt tilfelle kan du enkelt legge til tiden hver oppgave kommer til å ta til starttiden for å vite på hvilket tidspunkt oppgaven forventes å bli fullført.

Siden Excel lagrer dato- og klokkeslettverdier som tall, må du sørge for at tiden du prøver å legge til, overholder formatet som Excel allerede følger.

For eksempel, hvis du legger til 1 til en dato i Excel, vil den gi deg den neste datoen. Dette er fordi 1 representerer en hel dag i Excel (som er lik 24 timer).

Så hvis du vil legge til 1 time til en eksisterende tidsverdi, kan du ikke fortsette og bare legge til 1 til den. du må sørge for at du konverterer den timeværdien til desimaldelen som representerer en time. og det samme gjelder for å legge til minutter og sekunder.

Bruke TIME -funksjonen

Tidsfunksjonen i Excel tar timeverdien, minuttverdien og sekundverdien og konverterer den til et desimaltall som representerer denne tiden.

For eksempel, hvis jeg vil legge til 4 timer til en eksisterende tid, kan jeg bruke formelen nedenfor:

= Starttid + TID (4,0,0)

Dette er nyttig hvis du vet hvor mange timer, minutter og sekunder du vil legge til en eksisterende tid og bare bruke TID -funksjonen uten å bekymre deg for riktig konvertering av tiden til en desimalverdi.

Vær også oppmerksom på at TIME -funksjonen bare vil ta hensyn til heltallsdelen av timen, minuttet og sekundverdien du skriver inn. For eksempel, hvis jeg bruker 5,5 timer i TIME -funksjonen, vil det bare legge til fem timer og ignorere desimaldelen.

Vær også oppmerksom på at TIME -funksjonen bare kan legge til verdier som er mindre enn 24 timer. Hvis timeverdien din er mer enn 24, vil dette gi deg et feil resultat.

Og det samme gjelder minuttene og den andre delen der funksjonen bare vil vurdere verdier som er mindre enn 60 minutter og 60 sekunder

Akkurat som jeg har lagt til tid ved å bruke TIME -funksjonen, kan du også trekke fra tid. Bare endre + -tegnet til et negativt tegn i formlene ovenfor

Bruke grunnleggende regning

Når tidsfunksjonen er enkel og praktisk å bruke, kommer den med noen begrensninger (som dekket ovenfor).

Hvis du vil ha mer kontroll, kan du bruke den aritmetiske metoden som jeg dekker her.

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

For eksempel, hvis du vil legge til 24 timer til en eksisterende tidsverdi, kan du bruke formelen nedenfor:

= Starttid + 24/24

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

Når vi tar det samme konseptet fremover, la oss si at du vil legge til 30 timer til en tidsverdi, du kan bruke formelen nedenfor:

= Starttid + 30/24

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

På samme måte, hvis du har et bestemt antall minutter du vil legge til en tidsverdi, kan du bruke formelen nedenfor:

= Starttid + (minutter å legge til)/24*60

Og hvis du har antall sekunder du vil legge til, kan du bruke formelen nedenfor:

= Starttid + (minutter å legge til)/24*60*60

Selv om denne metoden ikke er like lett som å bruke tidsfunksjonen, synes jeg den er mye bedre fordi den fungerer i alle situasjoner og følger det samme konseptet. i motsetning til tidsfunksjonen, trenger du ikke bekymre deg for om tiden du vil legge til er mindre enn 24 timer eller mer enn 24 timer

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

Hvordan SUM tid i Excel

Noen ganger vil du kanskje raskt legge til alle tidsverdiene i Excel. Å legge til flere tidsverdier i Excel er ganske enkelt (alt som trengs er en enkel SUM -formel)

Men det er noen ting du trenger å vite når du legger til tid i Excel, spesielt celleformatet som skal vise deg resultatet.

La oss se på et eksempel.

Nedenfor har jeg en liste over oppgaver sammen med tiden hver oppgave vil ta i kolonne B, og jeg vil raskt legge til disse tidene og vite hvor lang tid det vil ta for alle disse oppgavene.

I celle B9 har jeg brukt en enkel SUM -formel for å beregne den totale tiden alle disse oppgavene kommer til å ta, og det gir meg verdien som 18:30 (som betyr at det kommer til å ta 18 timer og 20 minutter å fullføre alle disse oppgavene)

Alt bra så langt!

Hvordan SUMME over 24 timer i Excel

Se nå hva som skjer når jeg endrer på det tidspunktet det vil ta oppgave 2 å fullføre fra 1 time til 10 timer.

Resultatet sier nå 03:20, noe som betyr at det bør ta 3 timer og 20 minutter å fullføre alle disse oppgavene.

Dette er feil (åpenbart)

Problemet her er ikke å rote Excel. Problemet her er at cellen er formatert på en slik måte at den bare viser deg tidsdelen av den resulterende verdien.

Og siden den resulterende verdien her er mer enn 24 timer, bestemte Excel seg for å konvertere 24 -timersdelen til en dag, fjerne den fra verdien som vises for brukeren, og bare vise de resterende timene, minuttene og sekundene.

Dette har heldigvis en enkel løsning.

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

Nedenfor er noen formater du kan bruke:

Format	forventet resultat
[t]: mm	28:30
[m]: ss	1710:00
d “D” hh: mm	1 D 04:30
d “D” hh “Min” ss “Sek”	1 D 04 Min 00 Sek
d “Dag” hh “Minutt” s “Sekunder”	1 dag 04 minutt 00 sekunder

Du kan endre formatet ved å gå til dialogboksen formatceller og bruke det egendefinerte formatet, eller bruke TEKST -funksjonen og bruke et av de ovennevnte formatene i selve formelen

Du kan bruke TEKST -formelen nedenfor for å vise tiden, selv når den er mer enn 24 timer:

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

eller formelen nedenfor hvis du vil konvertere timene som overstiger 24 timer til dager:

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

Resultater som viser hash (###) i stedet for dato/klokkeslett (årsaker + løsning)

I noen tilfeller kan du oppdage at i stedet for å vise deg tidsverdien, viser Excel hash -symbolene i cellen.

Her er noen mulige årsaker og måter å fikse disse på:

Kolonnen er ikke bred nok

Når en celle ikke har nok plass til å vise hele datoen, kan den vise hash -symbolene.

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

Negativ datoverdi

En dato- eller klokkeslettverdi kan ikke være negativ i Excel. i tilfelle du beregner tidsforskjellen og det viser seg å være negativt, viser Excel deg hash -symboler.

Måten å fikse for å endre formelen for å gi deg det riktige resultatet. For eksempel, hvis du beregner tidsforskjellen mellom de to gangene og datoen endres, må du justere formelen for å ta hensyn til den.

I andre tilfeller kan du bruke ABS -funksjonen til å konvertere den negative tidsverdien til et positivt tall slik at den vises riktig. Alternativt kan du også bruke en IF -formel for å sjekke om resultatet er en negativ verdi og returnere noe mer meningsfylt.

I denne opplæringen dekket jeg emner om beregning av tid i Excel (hvor du kan beregne tidsforskjellen, legge til eller trekke fra tid, vise tid i forskjellige formater og sum tidverdier)

Jeg håper du synes denne opplæringen var nyttig.