Eftersom datum och tider lagras som nummer i backend i Excel kan du enkelt använda enkla räkneoperationer och formler för datum- och tidsvärden.
Till exempel kan du lägga till två olika tidsvärden eller datumvärden eller så kan du beräkna tidsskillnaden mellan två givna datum/tider.
I den här självstudien visar jag dig ett par sätt att utföra beräkningar med hjälp av tid i Excel (som att beräkna tidsskillnaden, lägga till eller subtrahera tid, visa tid i olika format och göra en summa tidsvärden)
Hur hanterar Excel datum och tid?
Som jag nämnde lagras datum och tider som nummer i en cell i Excel. Ett heltal representerar en hel dag och decimaldelen av ett tal skulle representera delen av dagen (som kan konverteras till timmar, minuter och sekunder)
Till exempel representerar värdet 1 01 januari 1900 i Excel, vilket är utgångspunkten från vilken Excel börjar överväga datumen.
Så 2 skulle betyda 02 januari 1990, 3 skulle betyda 03 januari 1900 och så vidare, och 44197 skulle betyda 01 januari 2021.
Obs! Excel för Windows och Excel för Mac följer olika startdatum. 1 i Excel för Windows skulle innebära 1 januari 1900 och 1 i Excel för Mac skulle innebära 1 januari 1904Om det finns några siffror efter en decimal i dessa siffror, skulle Excel betrakta dem som en del av dagen och det kan konverteras till timmar, minuter och sekunder.
Till exempel skulle 44197,5 innebära 01 januari 2021 12:00:00 PM.
Så om du arbetar med tidsvärden i Excel, skulle du i huvudsak arbeta med decimaldelen av ett tal.
Och Excel ger dig flexibiliteten att konvertera den decimala delen till olika format som bara timmar, bara minuter, bara sekunder eller en kombination av timmar, minuter och sekunder
Nu när du förstår hur tiden lagras i Excel, låt oss titta på några exempel på hur man beräknar tidsskillnaden mellan två olika datum eller tider i Excel
Formler för att beräkna tidsskillnad mellan två gånger
I många fall är det bara att ta reda på den totala tiden som har förflutit mellan de två tidsvärdena (t.ex. när det gäller en tidrapport som har In-time och Out-time).
Metoden du väljer beror på hur tiden nämns i en cell och i vilket format du vill ha resultatet.
Låt oss titta på ett par exempel
Enkel subtraktion av beräkna tidsskillnad i Excel
Eftersom tiden lagras som ett nummer i Excel, hitta skillnaden mellan 2 tidsvärden, kan du enkelt subtrahera starttiden från sluttiden.
Sluttid - Starttid
Resultatet av subtraktionen skulle också vara ett decimalvärde som skulle representera tiden som har gått mellan de två tidsvärdena.
Nedan följer ett exempel där jag har start- och sluttid och jag har beräknat tidsskillnaden med en enkel subtraktion.
Det finns en möjlighet att dina resultat visas i tidsformatet (istället för decimaler eller i timmar/minuter). I vårt exempel ovan visar resultatet i cell C2 09:30 istället för 9,5.
Det är helt okej eftersom Excel försöker kopiera formatet från den intilliggande kolumnen.
Om du vill konvertera detta till en decimal ändrar du cellens format till Allmänt (alternativet finns på fliken Hem i gruppen Numbers)
När du väl har resultatet kan du formatera det på olika sätt. Till exempel kan du visa värdet bara i timmar eller bara minuter eller en kombination av timmar, minuter och sekunder.
Nedan finns de olika format du kan använda:
| Formatera | Vad den gör |
| h | Visar bara de timmar som gått mellan de två datumen |
| hh | Visar timmar med tvåsiffriga (t.ex. 04 eller 12) |
| hh: mm | Visar timmar och minuter mellan de två datumen, till exempel 10:20 |
| hh: mm: ss | Visar timmar, minuter och sekunder mellan de två datumen, till exempel 10:20:36 |
Och om du undrar var och hur du använder dessa anpassade datumformat, följ stegen nedan:
- Markera cellerna där du vill använda datumformatet
- Håll ned Ctrl -tangenten och tryck på 1 -tangenten (eller Command + 1 om du använder Mac)
- I dialogrutan Formatera celler som öppnas, klicka på fliken Nummer (om den inte redan är markerad)
- Klicka på Custom i den vänstra rutan
- Ange någon av önskad formatkod i fältet Typ (i det här exemplet använder jag hh: mm: ss)
- Klicka på OK
Stegen ovan skulle ändra formateringen och visa dig värdet baserat på formatet.
Observera att anpassad nummerformatering inte ändrar värdet i cellen. Det ändrar bara hur ett värde visas. Så jag kan välja att bara visa timvärdet i en cell, medan det fortfarande skulle ha det ursprungliga värdet.
Proffstips: Om det totala antalet timmar överstiger 24 timmar, använd följande anpassade nummerformat istället: [hh]: mm: ssBeräkna tidsskillnaden i timmar, minuter eller sekunder
När du subtraherar tidsvärden returnerar Excel ett decimaltal som representerar den resulterande tidsskillnaden.
Eftersom varje heltal representerar en dag, skulle decimaldelen av talet representera den stora delen av dagen som enkelt kan omvandlas till timmar eller minuter eller sekunder.
Beräkna tidsskillnad i timmar
Anta att du har datauppsättningen som visas nedan och du vill beräkna antalet timmar mellan de två tidsvärdena
Nedan är formeln som ger dig tidsskillnaden i timmar:
= (B2-A2)*24
Ovanstående formel ger dig det totala antalet timmar som gått mellan de två tidsvärdena.
Ibland försöker Excel vara till hjälp och ger dig också resultatet i tidsformat (som visas nedan).
Du kan enkelt konvertera detta till Talformat genom att klicka på fliken Hem och i gruppen Nummer, välj Tal som format.
Om du bara vill returnera det totala antalet timmar som gått mellan de två tiderna (utan någon decimaldel), använd följande formel:
= INT ((B2-A2)*24)
Obs! Denna formel fungerar bara när båda tidsvärdena är samma dag. Om dagen ändras (där ett av tidsvärdena är av ett annat datum och det andra av ett annat datum), kommer denna formel att ge fel resultat. Ta en titt på avsnittet där jag täcker formeln för att beräkna tidsskillnaden när datumet ändras senare i den här självstudien.
Beräkna tidsskillnad i minuter
För att beräkna tidsskillnaden i minuter måste du multiplicera det resulterande värdet med det totala antalet minuter på en dag (vilket är 1440 eller 24*60).
Anta att du har en datamängd som visas nedan och du vill beräkna det totala antalet minuter som har gått mellan start- och slutdatum.
Nedan är formeln som gör det:
= (B2-A2)*24*60
Beräkna tidsskillnad i sekunder
För att beräkna tidsskillnaden i sekunder måste du multiplicera det resulterande värdet med det totala antalet sekunder på en dag (vilket är eller 24*60*60 eller 86400).
Antag att du har en datamängd som visas nedan och du vill beräkna det totala antalet sekunder som har gått mellan start- och slutdatum.
Nedan är formeln som gör det:
= (B2-A2)*24*60*60
Beräknar tidsskillnad med TEXT -funktionen
Ett annat enkelt sätt att snabbt få tidsskillnaden utan att oroa dig för att ändra formatet är att använda TEXT -funktionen.
Med TEXT -funktionen kan du ange formatet direkt i formeln.
= TEXT (Slutdatum - Startdatum, Format)
Det första argumentet är den beräkning du vill göra, och det andra argumentet är det format där du vill visa resultatet av beräkningen.
Anta att du har en dataset som visas nedan och du vill beräkna tidsskillnaden mellan de två tiderna.
Här är några formler som ger dig resultatet med olika format
Visa bara antalet timmar:
= TEXT (B2-A2, "hh")
Ovanstående formel ger dig bara resultatet som visar hur många timmar som gått mellan de två tidsvärdena. Om ditt resultat är 9 timmar och 30 minuter visar det fortfarande bara 9.
Visa antalet totala minuter
= TEXT (B2-A2, "[mm]")
Visa antalet totala sekunder
= TEXT (B2-A2, "[ss]")
Visa timmar och minuter
= TEXT (B2-A2, "[hh]: mm")
Visa timmar, minuter och sekunder
= TEXT (B2-A2, "hh: mm: ss")
Om du undrar vad som är skillnaden mellan hh och [hh] i formatet (eller mm och [mm]), när du använder hakparenteserna, skulle det ge dig det totala antalet timmar mellan de två datumen, även om timvärdet är högre än 24. Så om du subtraherar två datumvärden där skillnaden är mer än 24 timmar, kommer [hh] att ge dig det totala antalet timmar och hh kommer bara att ge dig de timmar som gått på dagen för slutdatumet .
Få tidsskillnaden i en enhet (timmar/minuter) och ignorera andra
Om du bara vill beräkna tidsskillnaden mellan de två tidsvärdena på bara antalet timmar eller minuter eller sekunder, kan du använda funktionen TIMMAR, MINUTER eller SECOND.
Var och en av dessa funktioner tar ett enda argument, vilket är tidsvärdet, och returnerar den angivna tidsenheten.
Anta att du har en datauppsättning som visas nedan, och du vill beräkna det totala antalet timmar minuter och sekunder som har gått mellan dessa två gånger.
Nedan finns formlerna för att göra detta:
Beräkna timmar som gått mellan två gånger
= TIMMA (B2-A2)
Beräkna minuter från tidsvärdesresultatet (exklusive de slutförda timmarna)
= MINUT (B2-A2)
Beräkna sekunder från tidsvärdesresultatet (exklusive de slutförda timmarna och minuterna)
= ANDRA (B2-A2)
Ett par saker som du behöver veta när du arbetar med dessa HOURS, MINUTE och SECOND formler:
- Skillnaden mellan sluttiden och starttiden kan inte vara negativ (vilket ofta är fallet när datumet ändras). I sådana fall skulle dessa formler returnera ett #NUM! fel
- Dessa formler använder bara tidsdelen av det resulterande tidsvärdet (och ignorerar dagdelen). Så om skillnaden i sluttid och starttid är 2 dagar, 10 timmar, 32 minuter och 44 sekunder, kommer HOUR -formeln att ge 10, MINUT -formeln ger 32 och den ANDRA formeln kommer att ge 44
Beräkna förfluten tid Till nu (från starttiden)
Om du vill beräkna den totala tiden som har gått mellan starttiden och den aktuella tiden kan du använda formeln NU istället för sluttiden.
NU -funktionen returnerar aktuellt datum och tid i cellen där den används. Det är en av de funktionerna som inte tar några ingångsargument.
Så om du vill beräkna den totala tiden som har gått mellan starttiden och den aktuella tiden kan du använda följande formel:
= NU () - Starttid
Nedan är ett exempel där jag har starttiderna i kolumn A, och tiden som har förflutit fram till nu i kolumn B.
Om tidsskillnaden mellan startdatum och tid och den aktuella tiden är mer än 24 timmar, kan du formatera resultatet för att visa dagen såväl som tidsdelen.
Du kan göra det genom att använda nedanstående TEXT -formel:
= TEXT (NU ()-A2, "dd hh: ss: mm")
Du kan också uppnå samma sak genom att ändra den anpassade formateringen av cellen (som täcktes tidigare i den här självstudien) så att den visar dagen såväl som tidsdelen.
Om din starttid bara har tidsdelen, skulle Excel betrakta det som tiden den 1 januari 1990.
I det här fallet, om du använder funktionen NU för att beräkna tiden som gått tills nu, kommer det att ge dig fel resultat (eftersom det resulterande värdet också skulle ha de totala dagar som har gått sedan 1 januari 1990).
I ett sådant fall kan du använda följande formel:
= NU ()- INT (NU ())- A2
Ovanstående formel använder INT -funktionen för att ta bort dagdelen från värdet som returneras av nu -funktionen, och detta används sedan för att beräkna tidsskillnaden.
Observera att NU är en flyktig funktion som uppdateras när det finns en ändring i kalkylbladet, men den uppdateras inte i realtid
Beräkna tid när datum ändras (beräkna och visa negativa tider i Excel)
De metoder som behandlas hittills fungerar bra om din sluttid är senare än starttiden.
Men problemet uppstår när din sluttid är lägre än starttiden. Detta händer ofta när du fyller i tidtabeller där du bara anger tiden och inte hela datumet och tiden.
I sådana fall, om du arbetar på ett nattskift och datumet ändras, finns det en möjlighet att din sluttid skulle vara tidigare än din starttid.
Om du till exempel börjar ditt arbete kl. 18.00 på kvällen och avslutar ditt arbete och timeout kl. 09.00 på morgonen.
Om du bara arbetar med tidsvärden kommer subtraheringen av starttiden från sluttiden att ge dig ett negativt värde på 9 timmar (9 - 18).
Och Excel kan inte hantera negativa tidsvärden (och för den delen inte heller människor, om du inte kan resa i tid)
I sådana fall behöver du ett sätt att räkna ut att dagen har förändrats och beräkningen bör göras därefter.
Tack och lov finns det en riktigt enkel fix för detta.
Antag att du har en dataset som visas nedan, där jag har starttid och sluttid.
Som du märker att ibland är starttiden på kvällen och sluttiden är på morgonen (vilket indikerar att detta var ett nattpass och dagen har ändrats).
Om jag använder nedanstående formel för att beräkna tidsskillnaden kommer det att visa mig hashtecknen i cellerna där resultatet är ett negativt värde (markerat med gult i bilden nedan).
= B2-A2
Här är en IF -formel om huruvida tidsskillnadsvärdet är negativt eller inte, och om det är negativt returnerar det rätt resultat
= IF ((B2-A2) <0,1- (A2-B2), (B2-A2))
Även om detta fungerar bra i de flesta fall, är det fortfarande kort om starttiden och sluttiden är mer än 24 timmar från varandra. Till exempel loggar någon in 9.00 på dag 1 och loggar ut 11.00 på dag 2.
Eftersom detta är mer än 24 timmar finns det inget sätt att veta om personen har loggat ut efter 2 timmar eller efter 26 timmar.
Även om det bästa sättet att ta itu med detta skulle vara att se till att uppgifterna innehåller datum såväl som tid, men om det bara är den tiden du arbetar med, bör formeln ovan ta hand om de flesta frågorna ( med tanke på att det är osannolikt att någon arbetar mer än 24 timmar)
Lägga till/ subtrahera tid i Excel
Hittills har vi sett exempel där vi hade start- och sluttiden, och vi behövde hitta tidsskillnaden.
Excel låter dig också enkelt lägga till eller subtrahera ett fast tidsvärde från det befintliga datum- och tidsvärdet.
Låt oss till exempel säga att du har en lista med uppgifter i kö där varje uppgift tar den angivna tiden och du vill veta när varje uppgift kommer att sluta.
I ett sådant fall kan du enkelt lägga till den tid som varje uppgift kommer att ta till starttiden för att veta vid vilken tidpunkt uppgiften förväntas slutföras.
Eftersom Excel lagrar datum- och tidsvärden som siffror måste du se till att tiden du försöker lägga till följer det format som Excel redan följer.
Om du till exempel lägger till 1 till ett datum i Excel, kommer det att ge dig nästa datum. Detta beror på att 1 representerar en hel dag i Excel (vilket är lika med 24 timmar).
Så om du vill lägga till 1 timme till ett befintligt tidsvärde kan du inte fortsätta och helt enkelt lägga till 1 till det. du måste se till att du konverterar det timvärdet till decimaldelen som representerar en timme. och detsamma gäller för att lägga till minuter och sekunder.
Använda TIME -funktionen
Tidsfunktionen i Excel tar timvärdet, minutvärdet och sekundvärdet och konverterar det till ett decimaltal som representerar denna tid.
Om jag till exempel vill lägga till 4 timmar till en befintlig tid kan jag använda följande formel:
= Starttid + TID (4,0,0)
Detta är användbart om du vet hur många timmar, minuter och sekunder du vill lägga till en befintlig tid och helt enkelt använda TID -funktionen utan att oroa dig för att tiden ska konverteras till ett decimalvärde.
Observera också att TIME -funktionen endast tar hänsyn till heltalet av det timme, minut och sekundvärde som du anger. Till exempel, om jag använder 5,5 timmar i TIME -funktionen skulle det bara lägga till fem timmar och ignorera decimaldelen.
Observera också att TIME -funktionen bara kan lägga till värden som är mindre än 24 timmar. Om ditt timvärde är mer än 24, skulle detta ge dig ett felaktigt resultat.
Och samma sak med minuterna och den andra delen där funktionen bara tar hänsyn till värden som är mindre än 60 minuter och 60 sekunder
Precis som jag har lagt till tid med funktionen TID, kan du också subtrahera tid. Ändra bara + -tecknet till ett negativt tecken i formlerna ovan
Använda grundläggande aritmetik
När tidsfunktionen är enkel och bekväm att använda har den några begränsningar (enligt ovan).
Om du vill ha mer kontroll kan du använda den aritmetiska metod som jag kommer att täcka här.
Konceptet är enkelt - konvertera tidsvärdet till ett decimalvärde som representerar delen av dagen, och sedan kan du lägga till det till valfritt tidsvärde i Excel.
Om du till exempel vill lägga till 24 timmar till ett befintligt tidsvärde kan du använda följande formel:
= Starttid + 24/24
Detta betyder bara att jag lägger till en dag till det befintliga tidsvärdet.
Nu tar vi samma koncept framåt, låt oss säga att du vill lägga till 30 timmar till ett tidsvärde, du kan använda följande formel:
= Starttid + 30/24
Ovanstående formel gör samma sak, där heltalets del av (30/24) skulle representera det totala antalet dagar under den tid du vill lägga till, och decimaldelen skulle representera timmarna/minuterna/sekunderna
På samma sätt kan du använda följande formel om du har ett visst antal minuter som du vill lägga till ett tidsvärde:
= Starttid + (minuter att lägga till)/24*60
Och om du har antalet sekunder som du vill lägga till kan du använda formeln nedan:
= Starttid + (minuter att lägga till)/24*60*60
Även om denna metod inte är lika lätt som att använda tidsfunktionen, tycker jag att den är mycket bättre eftersom den fungerar i alla situationer och följer samma koncept. till skillnad från tidsfunktionen behöver du inte oroa dig för om tiden du vill lägga till är mindre än 24 timmar eller mer än 24 timmar
Du kan följa samma koncept medan du också subtraherar tid. Ändra bara + till ett negativt tecken i formlerna ovan
Hur man summerar tid i Excel
Ibland kanske du snabbt vill lägga till alla tidsvärden i Excel. Att lägga till flera tidsvärden i Excel är ganska enkelt (allt som krävs är en enkel SUM -formel)
Men det finns några saker du behöver veta när du lägger till tid i Excel, särskilt cellformatet som kommer att visa dig resultatet.
Låt oss titta på ett exempel.
Nedan har jag en lista med uppgifter tillsammans med den tid varje uppgift kommer att ta i kolumn B, och jag vill snabbt lägga till dessa tider och veta den totala tiden det kommer att ta för alla dessa uppgifter.
I cell B9 har jag använt en enkel SUM -formel för att beräkna den totala tid som alla dessa uppgifter kommer att ta, och det ger mig värdet som 18:30 (vilket betyder att det kommer att ta 18 timmar och 20 minuter att slutföra alla dessa uppgifter)
Allt bra hittills!
Hur man summerar över 24 timmar i Excel
Se nu vad som händer när jag ändrar vid den tidpunkt det kommer att ta uppgift 2 att slutföra från 1 timme till 10 timmar.
Resultatet säger nu 03:20, vilket innebär att det bör ta 3 timmar och 20 minuter att slutföra alla dessa uppgifter.
Detta är fel (uppenbarligen)
Problemet här är inte att Excel trasslar till. Problemet här är att cellen är formaterad på ett sådant sätt att den bara visar dig tidsdelen av det resulterande värdet.
Och eftersom det resulterande värdet här är mer än 24 timmar, beslutade Excel att konvertera 24 -timmarsdelen till en dag ta bort den från värdet som visas för användaren och bara visa återstående timmar, minuter och sekunder.
Detta har tack och lov en enkel fix.
Allt du behöver göra är att ändra cellformatet för att tvinga det att visa timmar även om det överstiger 24 timmar.
Nedan följer några format som du kan använda:
| Formatera | Förväntat 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 "Minut" ss "Sekunder" | 1 dag 04 minut 00 sekunder |
Du kan ändra formatet genom att gå till dialogrutan formatceller och använda det anpassade formatet, eller använda TEXT -funktionen och använda något av ovanstående format i själva formeln
Du kan använda nedanstående TEXT -formel för att visa tiden, även om det är mer än 24 timmar:
= TEXT (SUMMA (B2: B7), "[h]: mm: ss")
eller nedanstående formel om du vill konvertera timmar som överstiger 24 timmar till dagar:
= TEXT (SUMMA (B2: B7), "dd hh: mm: ss")
Resultat som visar hash (###) istället för datum/tid (skäl + fix)
I vissa fall kan du upptäcka att istället för att visa tidsvärdet visar Excel hash -symbolerna i cellen.
Här är några möjliga orsaker och sätten att åtgärda dessa:
Kolumnen är inte tillräckligt bred
När en cell inte har tillräckligt med utrymme för att visa hela datumet kan den visa hash -symbolerna.
Den har en enkel fix - ändra kolumnbredden och gör den bredare.
Negativt datumvärde
Ett datum- eller tidsvärde kan inte vara negativt i Excel. om du beräknar tidsskillnaden och det visar sig vara negativt, visar Excel dig hash -symboler.
Sättet till korrigeringar för att ändra formeln för att ge dig rätt resultat. Om du till exempel beräknar tidsskillnaden mellan de två tiderna och datumet ändras måste du justera formeln för att ta hänsyn till den.
I andra fall kan du använda ABS -funktionen för att konvertera det negativa tidsvärdet till ett positivt tal så att det visas korrekt. Alternativt kan du också använda en IF -formel för att kontrollera om resultatet är ett negativt värde och returnera något mer meningsfullt.
I denna handledning täckte jag ämnen om beräkning av tid i Excel (där du kan beräkna tidsskillnaden, lägga till eller subtrahera tid, visa tid i olika format och summera tidsvärden)
Jag hoppas att du fann denna handledning användbar.
