Da datoer og tidspunkter er gemt som tal i bagenden i Excel, kan du nemt bruge simple regneoperationer og formler på dato- og tidsværdierne.
For eksempel kan du tilføje to forskellige tidsværdier eller datoværdier, eller du kan beregne tidsforskellen mellem to givne datoer/tidspunkter.
I denne vejledning viser jeg dig et par måder at udføre beregninger ved hjælp af tid i Excel (f.eks. Beregning af tidsforskellen, tilføjelse eller fradrag af tid, visning af tid i forskellige formater og udførelse af en sum tidsværdier)
Hvordan håndterer Excel dato og klokkeslæt?
Som jeg nævnte, gemmes datoer og tidspunkter som tal i en celle i Excel. Et helt tal repræsenterer en hel dag, og decimaldelen af et tal repræsenterer den del af dagen (som kan konverteres til timer, minutter og sekunder)
For eksempel repræsenterer værdien 1 01. januar 1900 i Excel, som er udgangspunktet, hvorfra Excel begynder at overveje datoerne.
Så 2 ville betyde 02. januar 1990, 3 ville betyde 03. januar 1900 og så videre, og 44197 ville betyde 1. januar 2021.
Bemærk: Excel til Windows og Excel til Mac følger forskellige startdatoer. 1 i Excel til Windows ville betyde 1. januar 1900 og 1 i Excel til Mac ville betyde 1. januar 1904
Hvis der er cifre efter et decimalpunkt i disse tal, betragter Excel dem som en del af dagen, og det kan konverteres til timer, minutter og sekunder.
For eksempel ville 44197,5 betyde 01. januar 2021 12:00:00 PM.
Så hvis du arbejder med tidsværdier i Excel, ville du i det væsentlige arbejde med decimaldelen af et tal.
Og Excel giver dig fleksibiliteten til at konvertere denne decimalandel til forskellige formater, f.eks. Kun timer, kun minutter, kun sekunder eller en kombination af timer, minutter og sekunder
Nu hvor du forstår, hvordan tiden gemmes i Excel, lad os se på nogle eksempler på, hvordan man beregner tidsforskellen mellem to forskellige datoer eller tidspunkter i Excel
Formler til beregning af tidsforskel mellem to gange
I mange tilfælde er alt, hvad du vil gøre, at finde ud af den samlede tid, der er gået mellem to-time-værdierne (f.eks. I tilfælde af en timeseddel, der har In-time og Out-time).
Den metode, du vælger, afhænger af, hvordan tiden nævnes i en celle, og i hvilket format du ønsker resultatet.
Lad os se på et par eksempler
Enkel subtraktion af Beregn tidsforskel i Excel
Da tiden er gemt som et tal i Excel, skal du finde forskellen mellem 2 tidsværdier, du kan let trække starttiden fra sluttiden.
Sluttid - Starttid
Resultatet af subtraktionen ville også være en decimalværdi, der repræsenterer den tid, der er gået mellem de to tidsværdier.
Nedenfor er et eksempel, hvor jeg har start- og sluttidspunktet, og jeg har beregnet tidsforskellen med en simpel subtraktion.
Der er en mulighed for, at dine resultater vises i tidsformatet (i stedet for decimaler eller i timer/minutter -værdier). I vores eksempel ovenfor viser resultatet i celle C2 09:30 i stedet for 9,5.
Det er helt fint, da Excel forsøger at kopiere formatet fra den tilstødende kolonne.
For at konvertere dette til en decimal skal du ændre formatet på cellerne til Generelt (indstillingen er på fanen Startside i gruppen Numbers)
Når du har resultatet, kan du formatere det på forskellige måder. For eksempel kan du vise værdien i kun timer eller minutter eller en kombination af timer, minutter og sekunder.
Nedenfor er de forskellige formater, du kan bruge:
| Format | Hvad det gør |
| h | Viser kun de timer, der er gået mellem de to datoer |
| hh | Viser timer med tocifrede (f.eks. 04 eller 12) |
| hh: mm | Viser timer og minutter mellem de to datoer, f.eks. 10:20 |
| hh: mm: ss | Viser timer, minutter og sekunder mellem de to datoer, f.eks. 10:20:36 |
Og hvis du undrer dig over, hvor og hvordan du anvender disse tilpassede datoformater, skal du følge nedenstående trin:
- Vælg de celler, hvor du vil anvende datoformatet
- Hold Ctrl -tasten nede, og tryk på 1 -tasten (eller Command + 1, hvis du bruger Mac)
- I dialogboksen Formater celler, der åbnes, skal du klikke på fanen Nummer (hvis den ikke allerede er valgt)
- Klik på Custom i den venstre rude
- Indtast en hvilken som helst af de ønskede formatkoder i feltet Type (i dette eksempel bruger jeg hh: mm: ss)
- Klik på OK
Ovenstående trin ændrer formateringen og viser dig værdien baseret på formatet.
Bemærk, at tilpasset talformatering ikke ændrer værdien i cellen. Det ændrer kun måden, hvorpå en værdi vises. Så jeg kan vælge kun at vise timeværdien i en celle, mens den stadig ville have den oprindelige værdi.
Pro tip: Hvis det samlede antal timer overstiger 24 timer, skal du bruge følgende brugerdefinerede talformat i stedet: [hh]: mm: ss
Beregn tidsforskellen i timer, minutter eller sekunder
Når du trækker tidsværdierne tilbage, returnerer Excel et decimaltal, der repræsenterer den resulterende tidsforskel.
Da hvert helt tal repræsenterer en dag, ville decimaldelen af tallet repræsentere den store del af dagen, som let kan konverteres til timer eller minutter eller sekunder.
Beregning af tidsforskel i timer
Antag, at du har datasættet som vist herunder, og du vil beregne antallet af timer mellem to-time-værdierne
Nedenfor er formlen, der giver dig tidsforskellen i timer:
= (B2-A2)*24
Ovenstående formel giver dig det samlede antal timer, der er gået mellem to-time-værdierne.
Nogle gange forsøger Excel at være nyttig og giver dig også resultatet i tidsformat (som vist nedenfor).
Du kan let konvertere dette til Talformat ved at klikke på fanen Startside og i gruppen Nummer vælge Nummer som format.
Hvis du kun vil returnere det samlede antal timer, der er gået mellem de to gange (uden decimaldel), skal du bruge nedenstående formel:
= INT ((B2-A2)*24)
Bemærk: Denne formel fungerer kun, når begge tidsværdier er for samme dag. Hvis dagen ændres (hvor en af tidsværdierne er for en anden dato og den anden for en anden dato), vil denne formel give forkerte resultater. Tag et kig på afsnittet, hvor jeg dækker formlen for at beregne tidsforskellen, når datoen ændres senere i denne vejledning.
Beregning af tidsforskel i minutter
For at beregne tidsforskellen i minutter skal du multiplicere den resulterende værdi med det samlede antal minutter på en dag (hvilket er 1440 eller 24*60).
Antag, at du har et datasæt som vist herunder, og du vil beregne det samlede antal minutter, der er gået mellem start- og slutdatoen.
Nedenfor er formlen, der gør det:
= (B2-A2)*24*60
Beregning af tidsforskel i sekunder
For at beregne tidsforskellen i sekunder skal du multiplicere den resulterende værdi med det samlede antal sekunder på en dag (hvilket er eller 24*60*60 eller 86400).
Antag, at du har et datasæt som vist nedenfor, og du vil beregne det samlede antal sekunder, der er gået mellem start- og slutdatoen.
Nedenfor er formlen, der gør det:
= (B2-A2)*24*60*60
Beregning af tidsforskel med TEKST -funktionen
En anden let måde hurtigt at få tidsforskellen uden at bekymre dig om at ændre formatet er at bruge TEKST -funktionen.
TEKST -funktionen giver dig mulighed for at angive formatet lige inden for formlen.
= TEKST (slutdato - startdato, format)
Det første argument er den beregning, du vil lave, og det andet argument er det format, hvor du vil vise resultatet af beregningen.
Antag, at du har et datasæt som vist nedenfor, og du vil beregne tidsforskellen mellem de to gange.
Her er nogle formler, der giver dig resultatet med forskellige formater
Vis kun antallet af timer:
= TEKST (B2-A2, "hh")
Ovenstående formel giver dig kun det resultat, der viser antallet af timer, der er gået mellem to-time-værdierne. Hvis dit resultat er 9 timer og 30 minutter, viser det dig stadig kun 9.
Vis antallet af samlede minutter
= TEKST (B2-A2, "[mm]")
Vis antallet af samlede 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 undrer dig over, hvad der er forskellen mellem hh og [hh] i formatet (eller mm og [mm]), når du bruger firkantede parenteser, vil det give dig det samlede antal timer mellem de to datoer, selvom timeværdi er højere end 24. Så hvis du fratrækker to datoværdier, hvor forskellen er mere end 24 timer, giver [hh] dig det samlede antal timer, og hh giver dig kun de timer, der er gået på dagen for slutdatoen .
Få tidsforskellen i en enhed (timer/minutter) og ignorer andre
Hvis du kun vil beregne tidsforskellen mellem de to tidsværdier i antallet af timer eller minutter eller sekunder, kan du bruge den dedikerede HOUR, MINUTE eller SECOND-funktion.
Hver af disse funktioner tager et enkelt argument, som er tidsværdien, og returnerer den angivne tidsenhed.
Antag, at du har et datasæt som vist nedenfor, og du vil beregne det samlede antal timer minutter og sekunder, der er gået mellem disse to gange.
Nedenfor er formlerne til at gøre dette:
Beregning af timer, der er gået mellem to gange
= TIME (B2-A2)
Beregning af minutter fra tidsværdieresultatet (eksklusive de afsluttede timer)
= MINUT (B2-A2)
Beregning af sekunder ud fra tidsværdieresultatet (eksklusive de gennemførte timer og minutter)
= ANDET (B2-A2)
Et par ting, du skal vide, når du arbejder med disse HOURS, MINUTE og SECOND formler:
- Forskellen mellem sluttidspunktet og starttiden kan ikke være negativ (hvilket ofte er tilfældet, når datoen ændres). I sådanne tilfælde ville disse formler returnere et #NUM! fejl
- Disse formler bruger kun tidsdelen af den resulterende tidsværdi (og ignorer dagdelen). Så hvis forskellen i sluttidspunkt og starttid er 2 dage, 10 timer, 32 minutter og 44 sekunder, vil HOUR -formlen give 10, MINUTE -formlen vil give 32, og den ANDRE formel vil give 44
Beregn forløbet tid til nu (fra starttidspunktet)
Hvis du vil beregne den samlede tid, der er gået mellem starttidspunktet og den aktuelle tid, kan du bruge formlen NU i stedet for sluttidspunktet.
NU -funktionen returnerer den aktuelle dato og klokkeslættet i cellen, hvor den bruges. Det er en af de funktioner, der ikke tager noget inputargument.
Så hvis du vil beregne den samlede tid, der er gået mellem starttiden og den aktuelle tid, kan du bruge nedenstående formel:
= NU () - Starttid
Nedenfor er et eksempel, hvor jeg har starttiderne i kolonne A, og tiden der er gået indtil nu i kolonne B.
Hvis tidsforskellen mellem startdato og klokkeslæt og den aktuelle tid er mere end 24 timer, kan du formatere resultatet til at vise dagen såvel som tidsdelen.
Du kan gøre det ved at bruge nedenstående TEKST -formel:
= TEKST (NU ()-A2, "dd hh: ss: mm")
Du kan også opnå det samme ved at ændre den tilpassede formatering af cellen (som dækket tidligere i denne vejledning), så den viser dagen såvel som tidsdelen.
Hvis din starttid kun har tidsdelen, ville Excel betragte det som tiden den 1. januar 1990.
I dette tilfælde, hvis du bruger funktionen NU til at beregne den tid, der er gået indtil nu, vil det give dig det forkerte resultat (da den resulterende værdi også ville have det samlede antal dage, der er gået siden 1. januar 1990).
I et sådant tilfælde kan du bruge nedenstående formel:
= NU ()- INT (NU ())- A2
Ovenstående formel bruger INT -funktionen til at fjerne dagdelen fra værdien returneret af funktionen nu, og denne bruges derefter til at beregne tidsforskellen.
Bemærk, at NU er en flygtig funktion, der opdateres, når der er en ændring i regnearket, men den opdateres ikke i realtid
Beregn tid, når datoen ændres (beregne og vis negative tider i Excel)
De metoder, der er omfattet indtil videre, fungerer godt, hvis din sluttid er senere end starttiden.
Men problemet opstår, når din sluttid er lavere end starttiden. Dette sker ofte, når du udfylder timesedler, hvor du kun indtaster tidspunktet og ikke hele dato og klokkeslæt.
I sådanne tilfælde er der mulighed for, at dit sluttidspunkt vil være tidligere end din starttid, hvis du arbejder i en one night shift og datoen ændres.
For eksempel, hvis du starter dit arbejde kl. 18:00 om aftenen og afslutter dit arbejde og timeout kl. 9:00 om morgenen.
Hvis du kun arbejder med tidsværdier, vil subtraktionen af starttiden fra sluttiden give dig en negativ værdi på 9 timer (9 - 18).
Og Excel kan ikke håndtere negative tidsværdier (og for den sags skyld heller ikke mennesker, medmindre du kan tidsrejse)
I sådanne tilfælde har du brug for en måde at finde ud af, at dagen er ændret, og beregningen skal foretages i overensstemmelse hermed.
Heldigvis er der en virkelig let løsning på dette.
Antag, at du har et datasæt som vist herunder, hvor jeg har starttidspunktet og sluttiden.
Som du vil bemærke, at nogle gange er starttidspunktet om aftenen, og sluttiden er om morgenen (hvilket indikerer, at dette var et nattevagt, og dagen har ændret sig).
Hvis jeg bruger nedenstående formel til at beregne tidsforskellen, viser det mig hash -tegnene i cellerne, hvor resultatet er en negativ værdi (markeret med gult i billedet herunder).
= B2-A2
Her er en HVIS formel, at uanset om tidsforskellen er negativ eller ej, og hvis den er negativ, returnerer den det rigtige resultat
= HVIS ((B2-A2) <0,1- (A2-B2), (B2-A2))
Selvom dette i de fleste tilfælde fungerer godt, mangler det stadig, hvis starttidspunktet og sluttiden er mere end 24 timer fra hinanden. For eksempel logger nogen ind kl. 9:00 på dag 1 og logger ud kl. 11:00 på dag 2.
Da dette er mere end 24 timer, er der ingen måde at vide, om personen har logget ud efter 2 timer eller efter 26 timer.
Selvom den bedste måde at tackle dette på er at sikre, at posterne indeholder både dato og klokkeslæt, men hvis det bare er den tid, du arbejder med, bør ovenstående formel tage sig af de fleste spørgsmål ( i betragtning af at det er usandsynligt for nogen at arbejde i mere end 24 timer)
Tilføjelse/ fradrag af tid i Excel
Indtil videre har vi set eksempler, hvor vi havde start- og sluttidspunktet, og vi havde brug for at finde tidsforskellen.
Excel giver dig også mulighed for nemt at tilføje eller fratrække en fast tidsværdi fra den eksisterende dato- og tidsværdi.
Lad os f.eks. Sige, at du har en liste over opgaver i kø, hvor hver opgave tager den angivne tid, og du vil vide, hvornår hver opgave slutter.
I et sådant tilfælde kan du nemt tilføje den tid, hver opgave vil tage, til starttiden for at vide, på hvilket tidspunkt forventes opgaven at blive udført.
Da Excel gemmer dato- og tidsværdier som tal, skal du sikre, at den tid, du forsøger at tilføje, overholder det format, som Excel allerede følger.
Hvis du f.eks. Tilføjer 1 til en dato i Excel, giver den dig den næste dato. Dette skyldes, at 1 repræsenterer en hel dag i Excel (hvilket er lig med 24 timer).
Så hvis du vil føje 1 time til en eksisterende tidsværdi, kan du ikke gå videre og blot tilføje 1 til den. du skal sørge for at konvertere denne timeværdi til den decimaldel, der repræsenterer en time. og det samme gælder tilføjelse af minutter og sekunder.
Brug af TIME -funktionen
Tidsfunktionen i Excel tager timeværdien, minutværdien og sekundværdien og konverterer den til et decimaltal, der repræsenterer denne tid.
For eksempel, hvis jeg vil tilføje 4 timer til en eksisterende tid, kan jeg bruge nedenstående formel:
= Starttid + TID (4,0,0)
Dette er nyttigt, hvis du ved, hvor mange timer, minutter og sekunder du vil føje til en eksisterende tid og blot bruge TID -funktionen uden at bekymre dig om den korrekte konvertering af tiden til en decimalværdi.
Bemærk også, at TIME -funktionen kun vil overveje den heltal del af den time, minut og sekunder værdi, du indtaster. For eksempel, hvis jeg bruger 5,5 timer i TIME -funktionen, ville det kun tilføje fem timer og ignorere decimaldelen.
Bemærk også, at TIME -funktionen kun kan tilføje værdier, der er mindre end 24 timer. Hvis din timeværdi er mere end 24, vil dette give dig et forkert resultat.
Og det samme gælder minutterne og den anden del, hvor funktionen kun vil overveje værdier, der er mindre end 60 minutter og 60 sekunder
Ligesom jeg har tilføjet tid ved hjælp af TIME -funktionen, kan du også trække tid fra. Skift bare + -tegnet til et negativt tegn i ovenstående formler
Brug af grundlæggende regning
Når tidsfunktionen er let og praktisk at bruge, kommer den med nogle få begrænsninger (som dækket ovenfor).
Hvis du vil have mere kontrol, kan du bruge den aritmetiske metode, som jeg vil dække her.
Konceptet er enkelt - konverter tidsværdien til en decimalværdi, der repræsenterer den del af dagen, og derefter kan du føje den til enhver tidsværdi i Excel.
Hvis du f.eks. Vil føje 24 timer til en eksisterende tidsværdi, kan du bruge nedenstående formel:
= Starttid + 24/24
Dette betyder bare, at jeg tilføjer en dag til den eksisterende tidsværdi.
Lad os nu sige det samme koncept fremad, at du vil tilføje 30 timer til en tidsværdi, du kan bruge nedenstående formel:
= Starttid + 30/24
Ovenstående formel gør det samme, hvor heltal delen af (30/24) repræsenterer det samlede antal dage i den tid, du vil tilføje, og decimaldelen repræsenterer timer/minutter/sekunder
På samme måde, hvis du har et bestemt antal minutter, som du vil føje til en tidsværdi, kan du bruge nedenstående formel:
= Starttid + (minutter til tilføjelse)/24*60
Og hvis du har det antal sekunder, du vil tilføje, kan du bruge nedenstående formel:
= Starttid + (minutter til tilføjelse)/24*60*60
Selvom denne metode ikke er så let som at bruge tidsfunktionen, finder jeg den meget bedre, fordi den fungerer i alle situationer og følger det samme koncept. i modsætning til tidsfunktionen behøver du ikke bekymre dig om, hvorvidt den tid, du vil tilføje, er mindre end 24 timer eller mere end 24 timer
Du kan følge det samme koncept, mens du også trækker tid fra. Skift bare + til et negativt tegn i formlerne ovenfor
Sådan SUM tid i Excel
Nogle gange vil du måske hurtigt tilføje alle tidsværdierne i Excel. Tilføjelse af flere tidsværdier i Excel er ganske ligetil (alt det kræver er en simpel SUM -formel)
Men der er et par ting, du skal vide, når du tilføjer tid i Excel, specifikt celleformatet, der viser dig resultatet.
Lad os se på et eksempel.
Nedenfor har jeg en liste over opgaver sammen med den tid, hver opgave vil tage i kolonne B, og jeg vil hurtigt tilføje disse tider og vide den samlede tid, det vil tage for alle disse opgaver.
I celle B9 har jeg brugt en simpel SUM -formel til at beregne den samlede tid, alle disse opgaver kommer til at tage, og det giver mig værdien som 18:30 (hvilket betyder, at det vil tage 18 timer og 20 minutter at fuldføre alle disse opgaver)
Alt godt indtil videre!
Sådan summeres over 24 timer i Excel
Se nu, hvad der sker, når jeg ændrer på det tidspunkt, det vil tage opgave 2 at fuldføre fra 1 time til 10 timer.
Resultatet siger nu 03:20, hvilket betyder, at det skal tage 3 timer og 20 minutter at fuldføre alle disse opgaver.
Dette er forkert (åbenbart)
Problemet her er ikke, at Excel roder. Problemet her er, at cellen er formateret på en sådan måde, at den kun viser dig tidsdelen af den resulterende værdi.
Og da den resulterende værdi her er mere end 24 timer, besluttede Excel at konvertere 24 timers delen til en dag, fjerne den fra den værdi, der vises for brugeren, og kun vise de resterende timer, minutter og sekunder.
Dette har heldigvis en let løsning.
Alt du skal gøre er at ændre celleformatet for at tvinge det til at vise timer, selvom det overstiger 24 timer.
Nedenfor er nogle formater, du kan bruge:
| Format | forventet resultat |
| [h]: 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 "Minute" ss "Sekunder" | 1 dag 04 minut 00 sekunder |
Du kan ændre formatet ved at gå til dialogboksen Formatceller og anvende det tilpassede format eller bruge TEKST -funktionen og bruge et af de ovenstående formater i selve formlen
Du kan bruge nedenstående TEKST -formel til at vise tiden, selv når den er mere end 24 timer:
= TEKST (SUM (B2: B7), "[h]: mm: ss")
eller nedenstående formel, hvis du vil konvertere timer, der overstiger 24 timer til dage:
= TEKST (SUM (B2: B7), "dd hh: mm: ss")
Resultater viser hash (###) i stedet for dato/klokkeslæt (årsager + rettelse)
I nogle tilfælde kan du opleve, at Excel i stedet for at vise dig tidsværdien viser hash -symbolerne i cellen.
Her er nogle mulige årsager og måder at løse disse på:
Kolonnen er ikke bred nok
Når en celle ikke har nok plads til at vise den fulde dato, viser den muligvis hash -symbolerne.
Det har en let løsning - skift kolonnebredden og gør den bredere.
Negativ datoværdi
En dato- eller tidsværdi kan ikke være negativ i Excel. hvis du beregner tidsforskellen, og det viser sig at være negativt, viser Excel dig hash -symboler.
Måden at rette op på for at ændre formlen for at give dig det rigtige resultat. For eksempel, hvis du beregner tidsforskellen mellem de to gange, og datoen ændres, skal du justere formlen for at tage højde for den.
I andre tilfælde kan du bruge ABS -funktionen til at konvertere den negative tidsværdi til et positivt tal, så den vises korrekt. Alternativt kan du også en IF -formel for at kontrollere, om resultatet er en negativ værdi og returnere noget mere meningsfuldt.
I denne vejledning dækkede jeg emner om beregning af tid i Excel (hvor du kan beregne tidsforskellen, tilføje eller trække tiden fra, vise tid i forskellige formater og sum tidværdier)
Jeg håber, at du fandt denne vejledning nyttig.
