Dato- og tidsberegninger i Excel

Dette er et uddrag af en artikel, som jeg tidligere har ud givet på Eksperten.dk, hvor den stadig findes i sin fulde udstrækning.

Enheden
Datoberegninger
Klokkeslætsberegninger
Arbejdstidsregistrering
1904-datosystemet

- Retur til Excel -

Enheden:
Som udgangspunkt for alle Excels tidsberegninger er en enhed. Denne enhed er 1 døgn. Alle andre tidsangivelser måles ud fra denne enhed. Uger, måneder og år er således multipla af et døgn, mens timer, minutter og sekunder er brøkdele af et døgn.

1 år = 365 eller 366 døgn afhængigt af, om der er skudår.
1 måned = 28, 29, 30 eller 31 døgn, afhængigt af, hvilken måned, der er tale om.
1 uge = 7 døgn.
1 time = 1/24 døgn = 0,041666666666…. døgn
1 minut = 1/1440 døgn = 0,0006944444…. døgn
1 sekund = 1/86400 døgn = 0,000011547407407407…. døgn

Ud fra disse værdier udarbejdes alle dato- og tidsmæssige beregninger. Det er dog vigtigt at datoer og klokkeslæt er indtastet i et format, som Excel genkender som en dato eller et klokkeslæt. Datoer indtastes således som dd-mm-åå eller dd/mm/åå. Ved indtastning er det muligt at udelade foranstillede 0’er i dag og måned. Klokkeslæt indtastes som tt:mm:ss, altså med kolon som skilletegn mellem enhederne. Derimod kan man ikke bruge punktum, hverken i datoer eller klokkeslæt.

Da alle dato- og tidsværdier altså repræsenterer tal, kan man også bearbejde dem med almindelige regnefunktioner så som addition, subtraktion, multiplikation og division. Det er dog ikke alle operationer, der er lige nemme at foretage, men det hænger sammen med dato/tids formatet, ikke værdierne.

- Til top -

Datoberegninger:
En af de ofte forekommende opgaver er at trække to datoer fra hinanden. Dette gøres ret nemt. Har man fx startdatoen i A1, fx 12-02-05 og slutdatoen, fx 15-02-05 i B1, beregnes antal dage som =B1-A1. Resultatet vil så blive vist som 03-01-1900. Cellen skal nu formateres som tal, hvorefter resultatet 3 vises.

Årsagen til visningen er, at når to datoformaterede celler indgår i en formel, bliver resultatet også datoformateret. Når det lige bliver 03-01-1900 skyldes det, at Excel som standard anvender 1900 datosystemet, som tager udgangspunkt i 1. januar 1900. Dette er dag 1. Døgn nummer 3 er således 3. januar 1900, og 3 er antallet af dage mellem de to datoer. Læg mærke til, at den sidste dag også tælles med. Reelt set er der jo kun 2 dage MELLEM de to datoer, og er det dette tal man er ude efter, kan man ændre sin formel til =B1-A1-1.

Hvis startdatoen er større end slutdatoen vises ikke noget resultat men ##############. Dette skyldes at resultatet bliver negativt, og Excel kan ikke vise negative dato- eller tidsangivelser. Men hvis cellen formateres som tal, står der fint -3 med datoerne i ovenstående eksempel.

Man kan også lægge datoer sammen, gange dem med hinanden eller dividere dem med hinanden, men det har sjældent noget fornuftigt formål.

Vi kan også kombinere beregninger med datoer og tal. Vi kan fx lægge dage til eller trække dem fra datoer.

12-02-05 + 2 = 14-02-05
12-02-05 – 2 = 10-02-05 osv.

Vi kan også gange og dividere, men igen er det ret meningsløst.

Excel har også en række indbyggede datofunktioner, som kan bruges til specielle udregninger. Dem skal jeg ikke komme detaljeret ind på i denne forbindelse, men de kan fx bruges, hvis man skal lægge et antal måneder eller år til en dato. 12-2-05 + 2 måneder, eller 12-2-05 + 2 år kan fx løses med:

=DATO(ÅR(A1);MÅNED(A1)+A2;DAG(A1))
hvor A1 indeholder datoen, og A2 det antal måneder, der skal lægges til.

=DATO(ÅR(A1)+A2;MÅNED(A1);DAG(A10))
hvor A1 stadig er datoen, mens A2 er det antal år, der skal lægges til.

- Til Top -


Klokkeslæts- eller tidsmæssige beregninger
Beregninger på klokkeslæt (tid) udføres på samme måde som datoberegninger. Forskellen er oftest, at i klokkeslætsberegninger vil vi gerne bevare klokkeslætsformatet.

Antag at
A1: 08:20:06
B1: 09:30:47

=B1-A1 giver 01:10:41
=B1+A1 giver 17:50:53

Vær opmærksom på, at hvis resultatet af en sammenlægning giver mere end 24, vises dette ikke som standard.

09:20 + 16:32 giver 01:52:00. For at få det vist som 25:52:00 skal cellen formateres med det brugerdefinerede format [t]:mm:ss.

- Til top -

Arbejdstidsregistrering
Arbejdstidsregistrering er ofte en ofte forekommende problemstilling i forbindelse med tidsberegninger. Her skal man igen huske at Excel ikke kan vise negative klokkeslæt.

Mødetid Står i A1, "Gå hjem" tid i B1

Så længe mødetid altid ligger før "gå hjem" tid er der ingen problemer. Så kan arbejdstiden beregnes som =B1-A1. Men hvis man arbejder hen over midnat, altså at mødetiden ligger efter gå hjem tiden, vil denne formel ikke virke, da den jo så giver et negativt resultat. Problemet kan løses ved at ændre formlen til =B1-A1+(A1>B1).

Hvis vi bruger den oprindelige formel og formaterer som tal, kan vi se, at vi får et negativt tal. Har vi fx 23:37 som mødetid og 2:37 som gå hjem tid vil resultatet formateret som tal være -0,875. Lægger vi 1 til det, bliver resultatet 0,125. Dette svarer til 1/8 af et døgn eller 3 timer, netop den tid, der er arbejdet.

Skal man lave tidsberegninger, som strækker sig over mere end et kalenderdøgn, fx at man møder én dag kl. 22:00 og går hjem ikke næste dag, men næste dag igen kl. 02:00 er man nødt til at tage datoen med.

Så kan man fx indtaste mødetid som 01-02-05 22:00 og gå hjem tid som 03-02-05 02:00. Så kan man bruge formlen ovenfor, altså =B1-A1+(A1>b1). Hvis man så formaterer cellen som [t]:mm:ss får man resultatet 28:00:00.

Lige som man kan lægge dage til en dato, kan man også lægge timer, minutter eller sekunder til et klokkeslæt. Har man fx 22:35 i A1 og vil lægge 1 time til, kan man bruge formlen: =A1+1/24. Skal man lægge 48 minutter til bliver formlen: =A1+48/1440 og skal man lægge 19 sekunder til skal formlen være =A1+19/86400.

Men hvad så, hvis man skal lægge 2 timer, 23 minutter og 46 sekunder til? Det kan gøre med =A1+2/24+23/1440+46/86400, men det nemmeste er at skrive =A1+"2:23:46".

- Til top -

1904-datosystemet
Nu har jeg flere gange nævnt at Excel ikke kan vise negative datoer eller klokkeslæt, og det er også delvist korrekt, hvis man anvender 1900-datosystemet, som er standard i Excel.  Excel kan faktisk godt vise hele negative timer, minutter eller sekunder. Formateres cellen som [tt] vises hele timer, også negative. [mm] giver hele minutter, mens [ss] giver hele sekunder. Desværre kan man ikke kombinere fx [tt]:[mm] eller [tt:mm]. Det tillades ikke, men skifter man til 1904-datosystemet, kan man godt vise negative datoer og klokkeslæt på helt "almindelig" vis.

Men skifter ved at vælge Funktioner - Indstillinger. I fanebladet beregning sættes flueben i 1904-datosystem. Nu kan negative datoer og klokkeslæt vises. Vær dog opmærksom på, at allerede indtastede datoer nu ændrer sig. For eksempel vil 19-04-2005 blive til 20-04-2009.

Det skyldes at i stedet for at anvende 01-01-1900 som udgangsdato, anvendes nu 02-01-1904.

- Til top -

- Retur til Excel -