Skip to the content

Datatype Date en het rekenen met datum/datums en tijd in Excel

In dit blog aandacht voor het rekenen met datum en tijd types door middel van functies in VBA voor Office toepassingen.

Omdat datum en tijd de meeste vragen en frustraties opleveren gaat dit hele blog over dit datatype. Rekenen met datums en tijd is niet moeilijk maar door de verschillen in taalinstellingen kan het een frustrerende klus zijn.

Afhankelijk van het formaat dat je krijgt aangeboden, 12 uur of 24 uur formaat, is het vrij eenvoudig om het verschil uit te rekenen tussen 11:00 en 16:00, maar het wordt al een stuk lastiger als je het verschil uit moet rekenen tussen 01-01-2012 10:00 en 06-01-2012 08:00.

Om dit te bereiken hebben we een 2 tal van functies nodig die we in de rest van het blog zullen gebruiken. Lees het blog over zelf functie maken in Excel als u niet weet hoe de VBA editor te openen en een module toe te voegen.

Eerst gaan we 2 constante waardes declareren op de bovenste regel van de module (of na de optie option explicit) in ieder geval voordat je begint met een functie of sub moeten de regels zijn gedeclareerd. We vertellen de module dat wanneer we de waarde dPeildatum aanroepen dit eigenlijk betekent dat de datum 1-1-2000 moet worden gebruikt. Ditzelfde geldt ook voor de constante lSecondenInEenDag die het aantal seconden weergeeft die een volle dag heeft.

Const dPeilDatum As Date = #1/1/2000#
Const lSecondenInEenDag As Long = 86400


De term Dim, wat decleration in memory betekent, vertelt de code dat het woord achter de term en de waarde die eraan wordt toegekend tijdelijk (variabele) moet worden opgeslagen in het geheugen.

'Onderstaande functie berekend het aantal seconden dat ligt tussen de constante Const dPeildatum en de meegegeven datum
'd = Datum en tijd zonder datatype aanduiding zodat we alles wat op een datum lijkt in kunnen sturen
'In de functie wordt geen foutafhandeling opgenomen omdat dat aan bod komt in een later blog.

Function Converteer_DatumTijd_Naar_Seconden(d)

    Dim lDagen As Long
    Dim lSeconden As Long
    
    'Bereken eerst het aantal seconden vanaf een bepaalde datum tot de datum die is meegegeven in d
    
    'DateDiff berekend de interval tussen 2 waarden van het datatype datum en tijd Waarbij:
    
    'De eerste parameter aangeeft wat er moet worden bepaald (in dit voorbeeld "d" = aantal dagen)
    
    'De 2e parameter de datum weergeeft vanaf waar moet worden berekend
    
    'De 3e parameter de datum weergeeft tot en met waar moet worden berekend
    
    lDagen = DateDiff("d", dPeilDatum, d)
    
    lSeconden = (lDagen * lSecondenInEenDag) + Converteer_Tijd_Naar_seconden(d)
    
    Converteer_DatumTijd_Naar_Seconden = lSeconden

End Function
'Functie zet de tijd uit een meegegeven datum om naar het aantal seconden dat is verstreken vanaf 00:00 op dezelfde dag
'd = Datum en tijd zonder datatype aanduiding zodat we alles wat op een datum lijkt in kunnen sturen

Function Converteer_Tijd_Naar_seconden(d)

    Dim lUur As Long
    Dim lMinuut As Long
    Dim lSeconden As Long
    Dim lRetourwaarde As Long
    
    lUur = Hour(d)
    
    lMinuut = Minute(d)
    
    lSeconden = Second(d)
    
    lRetourwaarde = (lUur * 3600) + (lMinuut * 60) + lSeconden
    
    Converteer_Tijd_Naar_seconden = lRetourwaarde

End Function


In de functies worden de variabelen gedeclareerd als het datatype long. Dit is noodzakelijk omdat de functie DateDiff() een waarde retourneert van het type long en anderzijds willen we conflicten tussen de diverse variabelen voorkomen. Immers een waarde die groter is dan de waarde die bijvoorbeeld het datatype integer toestaat veroorzaakt een fout in de functie.

Nu kunnen we diverse functies aanmaken om te rekenen met datum en tijd in VBA en als voorbeeld gaan we berekenen hoe lang een reis duurt tussen Amsterdam en Athene waarbij we vertrekken op 17-02-2012 10:30 en aan zullen komen op 19-02-2012 12:45 (we hebben rustig aan gedaan). We willen dus het aantal uren en minuten weten die de reis gaat duren en ook de mogelijkheid om de tijd die we overnachten te berekenen.

Hiervoor maken we een nieuwe functie die gebruik gaat maken van de 2 eerdere functies.

'Bereken de tijd (als numerieke waarde) in uren en minuten die ligt tussen 2 verschillende datum -en tijd waarden

Function Bereken_Uren(dag1, dag2, bIsRust As Boolean) As Double

    Dim lDag1 As Long
    Dim lDag2 As Long
    Dim lUren As Double
    
    lDag1 = Converteer_DatumTijd_Naar_Seconden(dag1)
    
    lDag2 = Converteer_DatumTijd_Naar_Seconden(dag2)
    
    lUren = (lDag2 - lDag1) / 3600
    
    If bIsRust = True Then
    
        Bereken_Uren = -lUren
    
    Else
    
        Bereken_Uren = lUren
    
    End If

End Function


We laten de functie een waarde retourneren van het datatype double omdat we ook achter de komma een waarde willen zien als deel van het uur. Zo moeten 15 minuten achter de komma een waarde van .25 tonen.

We converteren de waarden van beide datums en tijden nu eerst naar een waarde in seconden, gerekend vanaf de peildatum. We gebruiken hiervoor de hierboven gemaakte functie Converteer_DatumTijd_Naar_Seconden().

Vervolgens delen we het verschil in seconden van beide datum en tijd waarden door 3600 seconden. Er zitten 3600 seconden in een uur en we willen het aantal uren berekenen dat ligt tussen de twee parameters dag1 en dag2.

Tot slot retourneren we de berekende waarde, waarbij we gebruik maken van de extra parameter bIsRust die een waar/onwaar waarde bevat. Waarbij "waar" in de functie wordt meegegeven als een 1 en "onwaar" als een 0.

Reinder.eu | Rekenen met datum en tijd in Excel


In plaats van een “0” waarde voor onwaar en een “1” waarde voor waar in de bepaling van de rust kun je ook “onwaar” of “waar” gebruiken dit is aan jou.