RSS

Beregning af tid med VBA

På denne side vises eksempler på, hvordan man med VBA kan trække timer, minutter og sekunder ud af et datoformateret klokkeslæt som fx 03:40:51 (3 timer, 40 minutter og 51 sekunder). Du kan også se, hvordan man finder den forløbne tid mellem to datoer med klokkeslæt.

Man kan bruge VBA's strengfunktioner Left, Mid og Right (Metode 1) eller datofunktionen DatePart (Metode 2). Med datofunktionen DateDiff kan man tillige finde tidsforskellen mellem to tidspunkter, hvis de udover klokkeslættet også indeholder en dato (Metode 3).

Hvis du vil afprøve koden, markerer du den med musen, kopierer (CTRL+C) og indsætter den (CTRL+V) i et VBA modul.

Eksemplerne udspringer af en opgave på jobbet. Et anlæg havde (for) mange ikke-planlagte stop, og jeg ville finde den samlede stoptid i en periode.

Jeg kunne få klokkeslættene for stop og genstart, og dem indtastede jeg manuelt i to kolonner. Det så sådan ud:

A         B
Stop      Start
13:20:01  17:00:21
05:08:31  05:43:21
06:37:31  07:25:21
23:50:11  03:47:41
osv.

(Det kan være, at man skal formatere cellerne som brugerdefineret "hh:mm:ss".)

I Excel kan man trække klokkeslæt fra hinanden, altså: celle B2 - A2 = 03:40:20, men gør man det på den måde, får man et problem nede i 5. række, hvor vi passerer midnat.

Jeg indsatte derfor følgende formel i C2: "= 24 - A2 + B2" og kopierede nedad. Nu fik jeg den forløbne tid i timer, minutter og sekunder udtrykt som fx "03:40:20".

Man får imidlertid ikke noget ud af at lægge disse "klokkeslæt" sammen. Man må udtrække timer, minutter og sekunder hver for sig, for til slut at lægge dem sammen. Altså 03:40:20 til "3", "40" og "20".

Dertil kan man bruge regnearksfunktionerne "Time("C2")", "Minut("C2")" og "Sekund("C2")", men VBA er jo som skabt til den slags, og især da hvis det hele foregår i kode.

I det følgende viser jeg, hvordan man kan udtrække timer, minutter og sekunder af klokkeslættet ved at behandle klokkeslættet som en tekststreng (Metode 1) eller ved at bruge datofunktionen DatePart (Metode 2).

Hvis der er mere end 24 timer imellem de to tidspunkter, skal man udover klokkeslættet også have datoen med. Fx: "01-12-10 03:35:27" og i så fald skal man bruge datofunktionen DateDiff. Hvordan det gøres, skitserer jeg i Metode 3.

Hvis du vil afprøve koden, markerer du den med musen, kopierer (CTRL+C) og indsætter den (CTRL+V) i et VBA modul. Hvis du læser dette på en lille skærm, kan nogle af kodelinjerne se "knækkede" ud, men linjeskiftene er OK, når du indsætter i et VBA-modul.

Metode 1: Brug af string-funktioner


Sub BeregnTid()
'Læser de datoformaterede klokkeslæt og
'udtrækker timer, minutter og sekunder,
'så de kan lægges sammen.

Dim sString As String
Dim iTimer As Integer
Dim iMinutter As Integer
Dim iSekunder As Integer
Dim rTid As Range
Dim rCelle As Range

On Error GoTo ErrorHandle

'Finder første celle i kolonnen
'med datoformaterede klokkeslæt.
Set rTid = Range("C2")

'Udvider området til og med det
'sidste klokkeslæt i kolonnen.
Set rTid = Range(rTid, rTid.End(xlDown))

'Området rTid gennemløbes
For Each rCelle In rTid
   With rCelle
      'Da cellen indeholder en formel,
      'sætter vi vores strengvariabel =
      'cellens text property.
      sString = .Text

      'Timerne er de to første karakterer,
      'så vi bruger Left-funktionen.
      'Da "Left$", "Mid$" og "Right$" returnerer
      'datatypen "String", indsætter vi "Val" foran
      'for at konvertere til en numerisk værdi.
      iTimer = Val(Left$(sString, 2))

      'Minutterne er de to tal i position
      '4 og 5, så vi bruger Mid-funktionen.
      iMinutter = Val(Mid$(sString, 4, 2))

      'Sekunderne er de sidste to karakterer,
      'så vi bruger Right-funktionen.
      iSekunder = Val(Right$(sString, 2))

      'Timer, minutter og sekunder indsættes
      'i kolonnerne til højre for de
      'datoformaterede klokkeslæt.
      .Offset(0, 1).Value = iTimer
      .Offset(0, 2).Value = iMinutter
      .Offset(0, 3).Value = iSekunder
   End With
Next

BeforeExit:
Set rCelle = Nothing
Set rTid = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Metode 2: Brug af funktionen DatePart

DatePart er en af VBA's datofunktioner, som kan udtrække år, dato, måned, uge, timer, minutter og sekunder af en dato med (evt.) klokkeslæt. I det følgende bruges den på de rene klokkeslæt.


Sub BeregnTid2()
'Læser de datoformaterede klokkeslæt og
'udtrækker timer, minutter og sekunder,
'så de kan lægges sammen.

Dim sString As String
Dim iTimer As Integer
Dim iMinutter As Integer
Dim iSekunder As Integer
Dim rTid As Range
Dim rCelle As Range

On Error GoTo ErrorHandle

'Finder første celle i kolonnen
'med datoformaterede klokkeslæt.
Set rTid = Range("C2")

'Udvider området til og med det
'sidste klokkeslæt i kolonnen.
Set rTid = Range(rTid, rTid.End(xlDown))

'Området gennemløbes
For Each rCelle In rTid
   With rCelle
      'Da cellen indeholder en formel,
      'sætter vi vores strengvariabel =
      'cellens text property.
      sString = .Text

      'Nu bruges DatePart-funktionen til at
      'udtrække timer, minutter og sekunder.
      iTimer = DatePart("h", sString)
      iMinutter = DatePart("n", sString)
      iSekunder = DatePart("s", sString)

      'Timer, minutter og sekunder indsættes
      'i kolonnerne til højre for de
      'datoformaterede klokkeslæt.
      .Offset(0, 1).Value = iTimer
      .Offset(0, 2).Value = iMinutter
      .Offset(0, 3).Value = iSekunder
   End With
Next

BeforeExit:
Set rCelle = Nothing
Set rTid = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Metode 3

Hvis der er mere end 24 timer imellem to tidspunkter kan ovenstående metoder ikke anvendes. Så må man bruge datofunktionen DateDiff, og man må ud over klokkeslættet også angive datoen. Fx. 01-12-10 03:20:43

Hvis man har to sådanne tidspunkter, fx "01-12-10 03:20:43" og "03-12-10 17:01:22", kan man få forskellen i fx sekunder ved at skrive VBA-kode som denne:

lSekunder = DateDiff("s",Tidspunkt1, Tidspunkt2)

Hvor lSekunder er en variabel af datatypen long, og Tidspunkt1 og Tidspunkt1 er af datatypen Date. For mere information om, hvordan man bruger funktionen DateDiff til at finde forskellen i år, måneder, uger osv. se hjælpen i Excel VBA.

Hvis du udtrækker sekunder så brug datatypen Long. Tallene bliver hurtigt så store, at du får overflow, hvis du bruger Integer (max 32767) og ikke har en af de nyeste Excel-versioner, hvor VBA automatisk konverterer Integer til Long.

Jeg vil ikke afvise, at man kan finde forskellen mellem de to tidspunkter uden at bruge VBA, men jeg ved ikke lige hvordan, og skal jeg være ærlig, synes jeg også at VBA er sjovere!

Relateret: