Räkna med tid och klockslag i Excel


Räkna med tid är det många controllers och ekonomichefer som gör i Excel. Vanligast är det när man bygger upp olika former av lönesystem eller system för tidsredovisning. Hur många timmar har en person arbetat? Hur mycket övertid eller flextid innebär ett projekt?

När du skriver in en tid i Excel bör du separera mellan timmar, minuter och eventuella sekunder med kolon (tt:mm:ss). Tid bygger vidare på samma koncept som datum och är formaterade decimaltal. Decimaltalet 0,0 är klockan 00:00, 0,5 är 12:00 och 1,0 är 24:00. En timme är 1/24-dels dag. Formaterar du talet 1/24 (ca 0,0416667) till ett tidsformat (t.ex. tt:mm) så ser du att resultatet blir 01:00. En minut är på samma sätt 1/24/60-del (eller 1/1440-del) av ett dygn. 0,000694 formaterad med formatet tt:mm blir därför 00:01. På samma sätt som med datum kan man räkna med tid genom att subtrahera och addera en tid med en annan.  När man räknar med tid blir det också viktigt att vara medveten om hur olika formatkoder påverkar hur resultatet redovisas, t.ex. 1:45, 01:45 eller 01:45:00. För att ändra talformatet högerklickar du i cellen och välj Formatera celler i snabbmenyn. I bladfliken Tal väljer du önskat talformat i kategorin Tid, alternativt väljer du kategorin Anpassat och justerar talformatet manuellt. För att Excel ska visa 25:45 måste du ändra på talformatet i cellen som visar tiden. Välj Formatera celler, fliken Tal, Kategori Anpassat under. Välj formatet [t]:mm under Typ.

I följande film visar jag hur man kan arbeta med tid i Excel. En ekonomichef på en av mina kurser vill göra ett enkelt arbettidsschema i Excel där man kan skulle kunna räkna ut flextiden, dvs ett  enkelt system för tidredovisning med Excel. Den planerade arbetstiden är 8 timmar per dag (sluttid minus starttid minus lunchtid) och flexbufferten är skillnaden mellan faktisk arbetad tid och planerad. Det knepigaste här är nog hur själva flexbufferten skall beräknas. Det fungerar inte att skriva ”=8:00-F14” utan man måste göra om 8:00 till ett decimaltal. Det kan man göra på flera sätt, t.ex. med formeln ”=(8/24)-F14” (dvs 8 timmar är lika med 8/24 av ett dygn). Här väljer jag dock att skiva in tiden 8:00 och sedan ändra det till ett decimaltal genom att ändra talformatet till ett allmänt talformat.

Gör om sekunder till till timmar och minuter

Om du har ett heltal i en cell som representerar antal sekunder och vill visa hur många timmar, minuter och sekunder det blir kan du göra här:

  1. Dela summan med 86400 (antal sekunder på ett dygn), tex =A1/86400
  2. Välj Formatera celler (kortkommando Ctrl+1) och Tal kategori Anpassat skriv in det anpassade talformatet [t]:mm:ss.

Nu kommer du t.ex. kunna se att 6500 sekunder är det samma som en timme, 48 minuter och 20 sekunder (1:48:20).

Beräkna skillnaden mellan två tider som går över midnatt.

Om du vill räkna ut antal timmar mellan två tider som går över midnatt (t.ex. från 22:00 till 02:00) fungerar inte en enkel formel som sluttid minus starttid. ”02:00” minus ”22:00” ger en negativ tid och det omvända ”22:00” minus ”02:00” blir fel.
Formeln =B1-A1+(B1<A1) löser detta, där starttid är i cell A1 och sluttid är i cell B1.

Räkna avstånde mellan tider som kan gå över midnatt

(B1<A1) är ett logiskt test som är falskt om sluttiden är större än starttiden (dvs inte går över midnatt). Falskt är även det samma som noll. Om (B1<A1) är SANT, vilket är detsamma som siffran 1 så adderas 1 till sluttiden vilket gör att över-midnatt-problemet hanteras.

Är det något mer du vill veta om att räkna med datum eller tid? Skriv en kommentar!

Om Anders Isaksson

Jag är doktor i företagsekonomi och arbetar till vardags som univesitetslektor vid Chalmers tekniska högskola där jag forskar och undervisar i ämnen som redovisning, finansiering och kalkylering. Jag har arbetat med företagsanpassad utbildning i Excel i drygt 20 år och då framförallt mot personer i ekonomibefattningar (ekonomichefer, controllers, redovisningsansvariga, revisorer etc). Förutom min Excelbok (Excel för företagsekonomer, Liber förlag) har jag skrivit tre andra populära läroböcker och ett 40-tal vetenskapliga artiklar.
Det här inlägget postades i Excel och har märkts med etiketterna , , , , , , . Bokmärk permalänken.

22 kommentarer till Räkna med tid och klockslag i Excel

  1. Anders Isaksson skriver:

    Bonustips: Eller kanske mer ett förtydligande. Om du summerar två klockslag (t.ex. 14:00 med 15:00) så får du svaret 05:00 (dvs klockan 05:00). Vill du att svaret skall bli 29:00 (14+15) så måste du skapa det anpassade talformatet ([t]:mm) – Välj formatera celler med Ctrl+1, fliken Tal, Kategori Anpassat och skriv in [t]:mm i fältet Typ. Det bökigaste med detta är oftast att hitta tangenerna [ och ] (Alt gr 8 och 9 vanligen)

  2. Anders Isaksson skriver:

    Om du vill ha ett smart tips om hur du kan räkna sekunder och få svaret i timmar:minuter:sekunder-format så kan du kolla mitt exceltips 31: Räkna med sekunder

  3. Minus flex skriver:

    Hej Anders,
    Fick bra tips men har ytterligare en fråga. Hur gör jag för att visa de anställde hur månaga timmar de har minus på sin flex. Säg att de arbetat för lite och därför har en minus flexbuffert. För att personalen ska fårstå hur de ligger till vill jag visa detta som tid inte som ett decimal tal.

    Mvh
    Monica

  4. Minus flex skriver:

    Hej Anders,

    Jag hittade svaret själv
    Lösningen är att gå in i Arkiv (2010), välj Alternativ (2010), alternativet Avancerat, leta reda på avsnittet Vid beräkning av den här arbetsboken, bocka i alternativet, Använd Macintosh datumsystem. Då behöver man inte räkna om till decimaler utan kan göra allt i tid.
    Mvh
    Monica

    • Anders Isaksson skriver:

      Bra att det löste sig. Det är så man kan lösa problemet med negativ tid. Vad man skall tänka på att denna inställning är lokal på din excelinstallation och fungerar inte om någon annan som inte har den inställningen öppnar arbetsboken.
      mvh
      Anders

  5. Magnus skriver:

    Hej

    Jag har undrar om du vet om det är möjligt att tex skriva in klockslaget 1436 och att cellen automatiskt ändrar formatet till 14:36 så att man sparar lite tid när man sitter och matar in en massa klockslag? Jag har försökt en massa men inte hittat någon lösning.

    Magnus

    • Anders Isaksson skriver:

      Inget som gör det helt automatiskt. Men om du matar in 1436 i en kolumn och i en annan kolumn har funktionen =KLOCKSLAG(VÄNSTER(A1;2);HÖGER(A1;2);0) så kommer du få 14:36 där (om du formaterat det till tt:mm). I detta exempel så antas 1436 ligga i cell A1.
      /Anders

  6. Johannes Ernvik skriver:

    Hej Anders!

    Jag har fastnat på en grej som du säkert kan lösa enkelt.
    Följande formel fungerar då jag har cell C9 och D9 formaterade som tal, men då jag gör om dem till tidsformat händer inget i målcellen.
    Det jag är ute efter är att få Excel att automatiskt generera rastlängd vid olika längd på arbetstid.

    =OM(D9-C97;1;0,5))

    Mvh
    Johannes

    • Anders Isaksson skriver:

      Jag förstår inte exakt vad det logiska testet i din OM-funktion gör. Så därför skapar jag en variant.

      Säg att du har en starttid i cell A2 och en sluttid i cell B2, dessa är formaterade som tid, t.ex. 08:00 respektive 13:00. Sen har jag en OM-funktion som kollar om B2 minus A2 är större än 5 timmar. OM det är sant vill jag ha svaret 1:00 (en timmes rast) om det är falskt vill jag ha 0:30 (en halv timme rast).

      Min OM-funktion kommer då se ut så här: =OM((B2-A2)>0,208333333333333;0,0416666666666667;0,0208333333333333)

      Formaterar jag resultatet ifrån funktionen blir svaret korrekt 1:00 eller 0:30.

      Vad jag måste göra är att ta fram tidseriekoden för 5 timmar, 1 timme, 30 minuter.

      Det gör man enklast genom att skriva in tiderna med tidsformat och sedan formatera dessa till ett normalt talformat. Men man kan även räkna ut det, 5 timmar är 5/24-dygn = 0,208333333.

      Hoppas det kan ge dig en lösning till problemet.

      • Johannes skriver:

        Hej igen!
        Jag förstår att du inte kunde tolka formeln. Det försvann ett antal tecken då jag laddade upp den…
        Det där med format ja.
        Hur som helst fungerar ditt tips alldeles utmärkt vilket jag tackar för!
        Härligt med såna som du Anders som så frikostigt delar med sig av sina kunskaper!
        Det är så man vinner framgång!

        Med vänlig hälsning,
        Johannes

  7. Leif Johansson skriver:

    Hej Anders

    Frågan om att ange tid utan att trycka shift och kolon har jag till och från försökt lösa under många år. Nu äntligen ett tips och jag har testat det. Det fungerar utmärkt men kräver att som i det redovisade exemplet cell A1 har formatet text för siffrorna 1436. Frågan måste ställas om varför garageföretaget Microsoft inte kan lösa denna, till synes banala funktion, på ett enklare sätt. Här sitter säkerligen miljontals excelanvändare jorden runt och dagligen lägger in tider i scheman och produktionsflödesprotokoll med flera sammanställningar. Det torde röra sig som mängder av miljoner ”shift/kolon” tangenttryckningar som dagligen utförs till synes utan mening. När frågan då och då varit uppe har ”min IT-avdelning” bara lakoniskt konstaterat: ”det går inte”. Men det är ju en luttrad användare vana vid att höra från IT-avdelningen.

    Excel är mycket bra redskap när man väl får grepp om funktionerna, vilket inte alltid är lätt.

    Tack för hjälpen

    Leif

  8. Annsofi skriver:

    Hej!

    Vi har registrerat en persons ”tid för sänggående” under en period på 60 dgr och vill nu räkna ut ett medelvärde för ”tid för sänggåendet” (för att sedan kunna räkna ut den dagliga variationen utifrån det).
    Vi stöter på problem i excel då tider efter midnatt ger felaktiga medelvärden (ser ut som att personen gått och lagt sig extremt tidigt dessa dagar istället för sent..)
    Kan du se någon lösning på detta problem?
    Vänliga hälsningar
    Annsofi

    • Anders Isaksson skriver:

      Om man räknar ut medelvärdet av 23:00 och 01:00 så ger Excel svaret 12:00 fast det korrekta borde vara 24:00.
      Det beror på att om man bara skriver in tiden tt:mm så antar Excel att detta sker på samma dygn.
      En lösning kan vara att skriva in datum och klockslag (skriv in med formatet ÅÅÅÅ-MM-DD tt:mm). Medlet av 2013-04-24 23:00:00 och 2013-04-25 03:00:00 ger 2013-04-25 01:00 som medel eller 01:00 om man formaterar det som tid.
      Alternativt kan man lägga en kolumn bredvid tiderna och med en OM-funktion räkna upp alla morgontider med 1. Då kommer tidseriekoden bli samma tid nästa dygn och medelvärdet korrekt. Ett problem där är att man måste avgöra gränsen för nästa dygn =OM(A1>0,3;A1;A1+1) kommer att räkna upp alla tider före 7:12 som nästa dygn.

  9. Stefan skriver:

    Hej, i en tidrapport vill jag att Excel ska räkna ut följande.
    Om summan av antalet arbetade timmar är mindre än 166 vill jag att differensen ska bli ett negativt antal timmar, och om de överstiger 166 vill jag att diffrensen ska bli ett positivt antal timmar. Tacksam för hjälp med formeln och med formaterringen av cellerna.

    MVH/Stefan

    • Anders Isaksson skriver:

      Lite osäker på vad som skall beräknas, t.ex. om 166 är timmar (tidsserie) eller decimaltal. 166 timmar är ju det samma som decimaltalet 6,91666666666667
      men en OM-funktion borde väl kunna hantera detta, typ =OM(A1<166;”differens om mindre”;”differens om mer”)
      Ä

  10. Stefan Ternvald skriver:

    Hej, behöver hjälp med detta problem (texten på engelska då jag skickat den till ett forum, men ej fått svar):
    I want to calculate the diff between two times (result could be negative or positive values).
    If a person worked less the 166 hours/month it should show the diff in hours and mins as negative,
    and if more then 166 hours it should show the diff in hours and mins as positive.

    Cell F34 contains the TOTAL HOURS worked this month, formatted as Custom [t]:mm
    Cell M25 contains a CONSTANT of 166 hours, formatted as Custom [t]:mm
    Cell F35 contains the DIFF between the TOTAL HOURS and the CONSTANT, also formatted as Custom [t]:mm
    Formel in F35 is =SUM(ABS(F34-M25))

    I manage to get the DIFF value correct, except it doesn´t show the negative sign.
    I have Swedish settings in my computer, and Excel 2007.
    Any help would be appreciated.
    MVH/Stefan

    • Anders Isaksson skriver:

      Kanske är detta något som kan lösas genom att ändra till Macintosh datumsystem? Excel för Windows kan ha problem att räkna med negativa tider.
      Gå till Arkiv, Alternativ, Avancerat, Använd Macintosh datumsystem.

  11. Anders Larsson skriver:

    Hej
    Jag har ett litet problem. Jag vill räkna ut hur många timmer det är mellan två datum/klockslag (ex mellan 2013-01-02 13:08:00 och 2013-02-02 14:08:00). Samtidigt vill jag inte räkna med helger och timmar utanför arbetstid (08:00-17:00).
    Finns det någon som har gjort någon klurig formel kring detta?
    Mvh
    Anders

    • Anders Isaksson skriver:

      Har ingen direkt lösning och och hinner inte klura så mycket nu, men två input till en eventuell lösning kav vara:
      NETTOARBETSDAGAR är en funktion som räknar antal arbetsdagar mellan två dagar och kan hjälpa di att exckludera helger.
      =(A2-A1)*24 där A1 är startdatum och tid (dvs med formatet ÅÅÅÅ-MM-DD tt:mm) och A2 är slutdatum ger dig antal timmar mellan två datum (fast inkluderar helger och tid utanför arbetstid.
      Vet inte om det bidrog, men någon som ser detta kan kanske komplettera…
      mvh
      Anders

  12. Ingrid Håsteen skriver:

    Jämfört med ovanstående frågor känns min mycket enkel. Vi brukar ha en egen liten skidskyttetävling och där får vi olika många sekunders tidstillägg beroende på hur illa vi skjuter. T.e.x. ger en tia 0 sekunder, en femma 10 sekunder och en bom en hel minut, men jag lyckas inte multiplicera de angivna straffsekunderna(tt:mm:ss) med antalet träffar.

    • Anders Isaksson skriver:

      Om det bra finns tre alternativ (träff = 0 sekunder, 5 = 10 sekunder och bom = 60 sekunder) så kan man lösa det med en kapslad OM-funktion.
      Anta att man skriver in resultatet i cell A1 (10, 5 eller 0)
      =(OM(A1=10;0;OM(A1=5;10;60)))/24/60/60
      Jag delar resultatet med 24/60/60 för att göra om det till sekunder (24 timmar på ett dygn, 60 minuter på en timme och 60 sekunder på en minut).
      Formaterar resultatet som tid tt:mm:ss
      Summeras detta så får du resultatet i tt:mm:ss format som sedan kan adderas till åktiden.

      Exempel på hur det kan ställas upp:
      Tavla Träff Tid
      1 10 00:00:00
      2 10 00:00:00
      3 5 00:00:10
      4 5 00:00:10
      5 0 00:01:00
      Åktid 00:45:00
      Summa 00:46:20

Kommentarer inaktiverade.