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