En introduktion til simple beregninger med dato og tid er stærkt underdrevet.
Den er fyldestgørende vil jeg kalde den.
Dato- og tidsberegninger i Excel
En introduktion til simple beregninger med dato og tid i Excel. Opdateret med afsnit om beregning af tillæg.
Der kommer hen ad vejen temmelig mange spørgsmål der behandler beregninger med dato og tid. Derfor denne artikel, der prøver at beskrive nogle af de ting, man skal være opmærksom på ved dato/tidsberegninger i 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.
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.
12-02-05 + 15-02-05 = 01-04-2110 eller 76.793
12-02-05 * 15-02-05 = ######## eller 1.474.291.210.
"Havelågerne" skyldes at Excel ikke kender så store datoer. Den største dato Excel kender er 31-12-9999, og det svarer til 2.958.465. Den mindste dato Excel kender er altså 01-01-1900, der svarer til 1. Indtaster man datoer før 1. januar 1900, vil de ikke blive opfattet som datoer, mens som tekst, og man kan derfor ikke bruge dem i beregninger.
12-02-05 / 15-02-05 = 00-01-1900
hvilket umiddelbart er meningsløst, da der jo ikke er en dag 00 i januar. Fjerner man formateringen vil man kunne se, at det svarer til 0,999921871, altså knap et døgn, og formaterer man dette tal som klokkeslæt svarer det så til 23:59:53.
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 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, eller
=DATO(ÅR(A1)+A2;MÅNED(A1);DAG(A1))
hvor A1 stadig er datoen, mens A2 er det antal år, der skal lægges til.
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.
Arbejdstidsregistrering
Arbejdstidsregistrering er ofte et ofte forekommende emne på Eksperten. 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).
Forklaringen er at sidste parentes er et udsagn, der evalueres som sandt eller falsk. Prøv fx at skrive =A1>B1 i en celle. I det konkrete tilfælde evalueres udtrykket som sandt, fordi A1 er større end B1 (mødetid før gå hjem tid). Da SAND repræsenterer værdien 1, mens FALSK repræsenterer værdien 0, lægges der altså 1 til resultatet, hvis A1 er større end B1. 1 er grundenheden, altså et døgn.
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".
Beregninger at tillægsudløsende tid
Jeg er flere gange blevet spurgt om jeg ikke kunne forklare, hvordan man beregner overtid, nattillæg, aftentillæg osv. Dette har jeg veget tilbage for, da det er ret vanskeligt at sige noget konkret om, eftersom måden disse tillæg beregnes på, er meget forskellig fra virksomhed til virksomhed. Jeg har dog nu valgt at angive nogle forskellige eksempler, som man måske kan bygge videre på.
I alle eksemplerne gås ud fra, at "mødetid" indtastes i A1, mens "gå-hjem tid" indtastes i B1.
Eksempel 1: Der udbetales tillæg, for timer, der arbejdes efter 17.
Opgaven går her alene ud på, at beregne, hvor mange timer, der arbejdes efter kl. 17.00. Og allerede her melder den første udfordring sig. Nemlig om der skal tages hensyn til mødetiden også - altså om man kan møde ind efter 17, og alligevel få tillæg for alle timer, eller om man skal have arbejdet et minimums antal timer, for at få tillæg for timerne efter 17. Desuden er der forskel på, om man kan arbejde efter midnat, eller om man altid stopper før midnat, samt om tillægget på et tidspunkt hører op igen. Så lige pludselig har eksemplet udviklet sig til indtil flere variationer:
Eksempel 1a: Uanset hvornår man møder, udbetales tillæg for alle timer efter 17. Dog kun til midnat. Det løses forholdsvis nemt med denne formel:
=B1-MAKS(A1;(17/24))
"Gå-hjem tid"- det tal, der er størst af henholdsvis mødetid eller kl. 17:00). Møder man kl. 16:00 og går hjem kl. 19:00 gives tillæg for 2 timer, fordi klokken 17 er senere end mødetidspunktet. Møder man kl. 18:00 og går hjem kl. 19:00 får man tillæg for 1 time, fordi mødetidspunktet er senere end kl. 17.
Eksempel 1b: Der udbetales tillæg for alle timer efter kl. 17. Også timer efter midnat.
Dette er også forholdsvis simpelt. Ret formlen til:
B1-MAKS(A1;(17/24))+(A1>B1)
Der skal altså lægges 1 til, hvis "gå-hjem tid" ligger før mødetid, hvilket (A1>B1) sørger for.
Eksempel 1c: Der udbetales tillæg efter kl. 17.00, men kun for timer, der overstiger dagens normaltimetal (i eksemplet her sat til 7). Møder man således kl. 08:00 og går hjem kl. 18.00 udbetales 1 times tillæg. Møder man kl. 14.00 og går hjem kl. 18:00 udbetales ingen tillæg, fordi der ikke er arbejdet mere end 7 timer. Også denne situation kan have en variant, der går over midnat, men den må man selv arbejde sig frem til. Det burde være til at håndtere, hvis man ser på foregående eksempel.
=HVIS(B1-A1>(7/24);HVIS(B1>17/24;B1-(17/24);0);0)
løser problemet. Først undersøges, om der er arbejdet mere end 7 timer. I bekræftende fald beregnes tillægget for den tid, der ligger mellem kl. 17 og "gå hjem tiden"; i benægtende fald er tillægstiden 0:00.
Eksempel 2: Der udbetales tillæg for timer, der ligger før 06:00 og efter 17:00. Mødetid kl. 05:00 og "gå-hjem tid" kl. 19.00 skal således give 3 timer. Formlen er:
=(B1<=A1)*(1-(17/24)+(6/24))+MIN((6/24);B1)-MIN((6/24);A1)+MAKS((17/24);B1)-MAKS((17/24);A1)
Eksempel 3a: På lørdage, søndage og helligdage udbetales et ekstratillæg for timer mellem 11 og 17:
=HVIS(A1>=(17/24);0;HVIS(A1>=(11/24);MIN(B1;(17/24))-A1;MIN(B1;(17/24))-(11/24)))
Eksempel 3B: På lørdage, søndage og helligdage udbetales et forhøjet tillæg for timer efter kl. 17:
=B1-MAKS(A1;(17/24))+(A1>B1)
Og så videre. Mange af disse formler vil give fejl, hvis det ene eller begge felter ikke er udfyldt. De kan derfor alle suppleres med test for, om et eller begge felter er tomme, og der er indtastet tal mm. Eksempel 3a, med sådanne test indbyggede, kunne så se således ud:
=HVIS(ER.TAL(HVIS(ELLER(ER.TOM(A1);ER.TOM(B1))=SAND;"";(B1<=A1)*(1-(17/24)+(6/24))+MIN((6/24);B1)-MIN((6/24);A1)+MAKS((17/24);B1)-MAKS((17/24);A1)));HVIS(ELLER(ER.TOM(A1);ER.TOM(B1))=SAND;"";(B1<=A1)*(1-(17/24)+(6/24))+MIN((6/24);B1)-MIN((6/24);A1)+MAKS((17/24);B1)-MAKS((17/24);A1)))
De ovenstående eksempler tager udgangspunkt i mødetid/"gå-hjem tid", men man kan også forestille sig situationer, hvor det alene er varigheden af arbejdet, der udløser tillæg, uafhængigt af, hvornår man arbejder - eller evt. i kombination med arbejdstiden.
De faste tider, fx 6, 11 og 17, kan selvfølgelig skiftes ud med andre. Eller måske bedre, de kan erstattes med cellereferencer, hvor standardtiderne så kan indtastes.
Som skrevet ovenfor, er mulighederne legio, så den nemmeste måde at få løst et konkret problem på, er enten at prøve sig frem med Excels mange muligheder, eller at beskrive sit problem detaljeret, og så stille spørgsmålet under Regneark.
Som det kan ses er der mange muligheder for tidsberegninger, men det vigtigste er at huske grundtallene, nævnt først i artiklen.
1904-datosystemet
Nu har jeg flere gange nævnt at Excel ikke kan vise negative datoer eller klokkeslæt, og det er ogsåp korrekt, hvis man anvender 1900-datosystemet, som er standard i Excel. Men skifter man til 1904-datosystemet, kan man godt vise negative datoer og klokkeslæt.
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.
Tilføjelse: 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.
Saxet fra experten.dk Hvis jeg husker rigtigt
Højris