Negativ tid i Excel

Många som arbetar med tid i Excel har säkert träffat på problemet med negativ tid eller minustid i Excel. Om man beräknar en tidpunkt minus en annan tidpunkt som ger ett negativt värde kan Excel som default inte hantera detta. Programmet visar ####### som resultat i cellerna (markerat i rött nedan). Det kan tyckas nästan genant att ett så grymt bra program inte klarar denna till synes enkla beräkning och är frustrerande för många användare.

Det finns dock tre (minst) lösningar på problemet. Nedanstående lösningar har dock sina respektive nackdelar, så använd metod efter behov.

Exemplet nedan visar en tidredovisning där in- och utstämpling skett för en anställd två gånger per dag under en tidsperiod. Den ordinarie arbetstiden är angiven i cell D2 och avvikelsen per dag och total avvikelse är angiven i J-kolumnen. Nedan har tre dagar en mindre faktisk arbetstid än ordinarie arbetstid (markerat i rött).

Klicka här för att Tips2014-Beräkning av negativ tid i Excel. (Filen ligger sparad med Macintosh datumsystem förvalt).

Använd Macintosh datumsystem

Klicka på ARKIV och Alternativ och välj Avancerat. Skrolla sedan ner till avsnittet Vid beräkning av den här arbetsboken. Kryssa i kryssrutan Använd Macintosh datumsystem.

Det som då sker är att programmets datumuppsättning ändras från att börja 1900-01-01 till att börja från 1904-01-01. Ett problem som då uppstår är att alla befintliga datum kommer att öka med 1462 dagar. Så vårt första datum som tidigare var 2015-04-01 har förvandlats till 2019-04-02. (se bilden nedan).

För att lösa detta problem behöver vi subtrahera alla datum med 1462. Ett sätt att göra detta är att skriva in 1462 i en cell. Kopiera sedan denna cell. Markera alla datum och högerklicka och välja Klistra in special…. Kryssa i Värden (för att behålla ursprungligt format för datumcellerna) och kryssa i Subtrahera. Klicka sedan OK.

Nu ser vi att vår tabell innehåller korrekta datum och beräkningarna för alla avvikelser – även de negativa – blir korrekt visade i J-kolumnen. 🙂

Kom ihåg att inställningen för Macintosh datumsystem gäller för alla blad i den aktuella arbetsboken.

Använd funktionen TEXT

Om du av någon anledning har svårigheter att använda Macintosh datumsystem kan man använda funktionen TEXT tillsammans med OM-funktionen för att visa negativa tidsvärden. I cell J5 har följande formel skrivits in och kopierats till övriga avvikelseceller.

=OM($D$2-I5>0;TEXT(MAX($D$2;I5)-MIN($D$2;I5);”-tt:mm”);I5-$D$2)

Viktigt! Notera att funktionen text omvandlar talvärden till text vilket gör att summeringen av avvikelsecellerna endast summerar de positiva avvikelserna. Dock visas alla negativa värden i tabellen.

Villkorsstyrd formatering

Ett tredje sätt är att använda villkorsstyrd formatering. Denna metod fungerar bara om du vill visa (inte beräkna) den negativa tiden. Dessutom kräver metoden att du alltid minskar den tidigare tidpunkten med den senare tidpunkten. Med detta menas att alla negativa tider visas som positiva tider.

Välj Formatera celler… och Anpassat. Skriv in ”-t:mm” i fältet Typ: och klicka OK

Dela gärna på FB, LinkedIn eller andra social medier om du tror att andra kan dra nytta av tipset.

Av Tobias Ljung