Räkna med datum och tid 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.

Nya filmer om att räkna med tid I Excel

Se mina nya filmer om att räkna med tid i Excel!

På YouTube har jag en äldre film om hur man gör ett tidsschema i Excel som räknar ut arbetad tid samt eventuell flextid. Filmen spelades in för att hjälpa en kursdeltagare med ett praktiskt problem. Jag saknade mikrofon så det blev en tyst instruktion som vissa kanske kan uppleva som lite svår att hänga med på. Trots det så har den visas över 23000 gånger… Så uppenbart verkar den ha något som intresserar trots det i mitt tycke rätt bedrövliga produktionen…

Så, nu har jag spelat in två nya filmer om detta som förhoppningsvis är lite bättre i alla avseenden. Inte minst för att de har ljud och kanske är lite mer pedagogiska – det har ändå gått över sju år sedan jag gjorde dem så jag har nog lärt mig lite mer….

Ni hittar dem här

Räkna med tid i Excel: Grunderna: https://youtu.be/lw3cnY0nf1I

Räkna med tid i Excel: Fördjupning: https://youtu.be/9PHJ-_qtoso

Arbetsboken 012_Räkna_med_tid_i_Excel.xlsx som jag använder i filmerna kan du hämta från min exempelsida: https://www.andersexcel.se/excelskolan/filer/

En tredje film:

Här är ytterligare en film där jag visar nya sätt att räkna antal hela år:

Räknar antal år och dagar – I Excel – https://youtu.be/mHWs6Gcweb4

 

Om du har nytta av det material jag gjort så skulle jag uppskatta om du hjälper mig få fler tittare till dessa filmer genom att klicka på Gilla och prenumerera på min kanal. Det kostar inte dig något, men gör mig lite gladare…. Här kan du prenumerera: http://www.youtube.com/subscription_center?add_user=AndersExcel

Vill du stötta mitt arbete i att leverera excelkunskap på svenska går det bra att bjuda på en kopp kaffe: https://www.buymeacoffee.com/andersexcel

Räkna ut antal dagar mellan två datum

Det enklaste beräkningen är att räkna ut hur många dagar det är mellan två datum.
I och med att Excel arbetar med tidseriekoder där varje ny dag motsvarar 1 så kan man enkelt räkna ut skillnaden att skriva en formel som tar det större datumen minus det mindre.

Om du t.ex. i cell A1 har skrivit in 2019-12-01 och i cell A2 skrivit in 2019-12-24 så kan du med formeln =A2-A1 få reda på att det är 23 dagar kvar till julafton. Notera att man inte inkluderar startdatumet: dvs om vi befinner oss på den 1 december och lägger till 23 dagar så kommer datumet bli 24 december. Vill du inkludera datumet du befinner dig i så kan du bara lägga till… ”+1”, dvs =A2-A1+1.

Notera att du inte kan skriva båda datumen i formeln direkt. ”=2019-12-21-2019-12-24” fungerar inte utan blir 2019 minus 12 minus 21 osv = -69.

Vill du räkna ut skillnaden i dagar mellan två datum och exkludera helgdagar kan du använde funktionen NETTOARBETSDAGAR. Det kan vara användbart t.ex. vid planering av ett projekt då man vill veta hur många arbetsdagar man har på sig från ett datum till ett annat. Om vi försätter vårt exempel så kommer =NETTOARBETSDAGAR(A1;A2) ge svaret 17. Formeln som den har skrivits här räknar enbart lördagar och söndagar som lediga dagar, men man kan lägga till andra datum som också är lediga som ett tredje argument. Läs mer om detta på Microsoft sida för funktionen NETTOARBETSDAGAR.

Lite svårare blir det om du vill räkna ut antal år, månader eller veckor mellan två datum.

Räkna ut antal år, veckor eller månader mellan två datum

Lite svårare blir det om du vill räkna ut antal år, månader eller veckor mellan två datum.

Problemet med att räkna ut antal år, månader eller veckor mellan två datum är att vi inte har ett tidsystem som är jämt delbart (med t.ex. 10). Månader kan ha 28, 29, 30 eller 31 dagar. Ett år är tolv månader osv.

När det gäller år så vill man t.ex. veta antal hela år. Om du t.ex. är född den sista december så har du ju inte fyllt är förrän detta datum, men om du tar ditt födelseår minus årets år så får du för det mesta fel antal år.

Den smidigaste lösningen på detta är att använda den lite dolda Excelfunktionen DATDIF.

Jag har skrivit ett helt blogginlägg om detta som du kan läsa här: Avståndet mellan två datum med DATEDIF

Räkna ut antal exakta hela år mellan två datum med funktionen ÅRDEL (YEARFRAC)

Antag att en person är född 1965. Då kommer funktionen =ÅR(IDAG())-1965 ge ålder i antal år. Här används såldes funktionen IDAG som ett argument för funktionen ÅR för att hämta det aktuella året och subtraherar sedan 1965, vilket returnerar personens ålder.

Problemet med denna funktion kan emellertid vara att den inte tar hänsyn när på året en person är född. Om man t.ex. är född den 1:a december 1965 och dagens datum är 2022-22-18 så kommer funktionen fortfarande ge svaret 57, även om personen ännu inte fyllt 57 år. Vilket kan vara ett problem för vissa.

Ett alternativ är att använda den relativt nya funktionen ÅRDEL (eng. YEARFRAC). ÅRDEL beräknar det bråktal som representeras av antalet fullständiga dagar mellan två datum (startdatum och stoppdatum).
Så om jag använder följande funktion (där födelsedagen ligger i cell A1):
=ÅRDEL(A1;IDAG())

Så kommer svaret bli 56,96 (om en person är född 1965-12-01 och dagens datum är 2022-11-18.

Svaret kan sedan avrundas nedåt med funktionen AVRUNDA.NEDÅT för att ge ett heltal:
=AVRUNDA.NEDÅT(ÅRDEL(A1;IDAG());0)

Ett annan (kanske mer praktiskt ekonomisk) användning av funktionen ÅRDEL är att att identifiera andelen av ett helt års inkomster och utgifter för en viss tidsperiod.

Här är en film där jag använder ÅRDEL för att räkna ut hur gammal min kompis Peder är:

Räkna ut antal av en viss veckodag för en viss månad

Från det jag skrivit ovan så bör du nu veta hur man kan räkna ut hur många dagar det är en viss månad. Men om du vill veta hur många dagar det är av en viss veckodag en viss månad… Hur gör du då?

Det här är ett lite knepigt problem som jag fick en fråga om. Lösningen ger en inblick hur man kan arbeta med mer komplicerade frågeställningar som rör att räkna med datum.

Frågan:  Hur får jag fram hur många måndagar det är på en månad? tex juli 2020? (svaret ska bli 4)

Svar: För det första måste det datum du skall räkna på måste vara inmatat som ett datum, t.ex. 2020-07-01. Dvs. Excel måste läsa detta som en tidsseriekod. Vill man att det t.ex. skall visas som ”Juli 2020” så får man ändra det med ett anpassat talformat MMMM ÅÅÅÅ

För att denna formel skall fungera så måste datumet vara den 1:a i varje månad.

Antag att datumet ligger i cell A1

Med följande formel så kommer antal måndagar i månaden att visas

=AVRUNDA.UPPÅT((DAG(SLUTMÅNAD(A1;0))+VECKODAG(A1+5))/7;0)-1

En kort förklaring är att den utgår från att det bara kan vara 4 eller 5 måndagar i en månad.

DAG(SLUTMÅNAD(A1;0)) ger hur många dagar det är i månaden. I detta fall 31.

VECKODAG(A1+5) är en liten ”knorr” som räknar upp antalet och hanterar besvärligheter som t.ex. månader som är 31 dagar och börjar på en lördag… Skulle du istället vilja räkna fredagar kan man ändra +5 till +1.

Så summan av DAG(SLUTMÅNAD(A1;0))+VECKODAG(A1+5)) ger dig det totala antal dagar mellan den sista dagen i månaden och den sista måndagen i föregående månad. Detta delas med 7 för att få antal veckor och minus ett för att vi inte bryr oss om förra månades måndag.

Gör om sekunder 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.

 

 

Omvandla månadsnamn i textformat till datum i Excel

Ibland så hamnar man i ett läge att man har datum i textformat. T.ex. att man har en lista med månadsnamn (Jan, Feb, Mar, Apr osv) och behöver omvandla dessa till ett korrekt datum.

I detta fall kan funktionen DATUM vara användbar.

Funktionen DATUM har tre argument, År, Månad och Dag. Dessa argument anges som siffervärden (heltal) som motsvarar år (från 1900 och uppåt), månad (1 till 12) och dag (1 till 31).

I följande film så visar jag hur man men hjälp av funktionen DATUM (och med stöd av LETARAD) kan omvandla månadsnamn i textformat till datum i Excel:

 

 

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

Klicka gärna på Gilla! (ligger under rubriken) om du gillar mina inlägg och vill hjälpa andra att hitta hit.

PS! Dagens boktips:
En bok jag kan rekommendera just nu är Jelen, Excel 2016 In Depth (QUE Förlag) . En heltäckande bok skriven av en av mina excelgurus, Bill ”MrExcel” Jelen (kolla MrExcels kanal på YouTube!). Är du nybörjare med Excel och vill ha en bok på svenska som ger dig grunderna kan jag rekommendera Excel 2016 Grunder (Eva Ansell) som också har skrivit Excel 2016 Fördjupning, en bok som jag själv brukar använda på mina Excelkurser.
.

Räkna med tid i Excel: Grunderna
Räkna med tid i Excel: Fördjupning: Hur du gör ett arbetstidsschema med övertid
(Visited 67 294 times, 16 visits today)

22 comments

  • 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.

    • 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

  • 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

    • 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

  • Stefan Ternvald

    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

    • 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.

  • 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

    • 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")
      Ä

  • 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

    • 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.

  • Leif Johansson

    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

  • Johannes Ernvik

    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

    • 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.

      • 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

  • 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

    • 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

  • 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

    • 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

  • 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

  • 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

  • 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)