Alle beste datum/tijd VBA functies op een rij

In dit artikel gaan wij het hebben over hoe je omgaat met het data type datum en tijd binnen VBA. Van alle verschillende soorten data in VBA heb ik in de praktijk de meeste problemen gehad met het type datum. Dit had vaak te maken met het feit dat datums binnen Excel niet altijd herkend worden als datums, maar gezien worden als string (tekst). Ik zal hier verderop in dit artikel een praktijkvoorbeeld van laten zien. We beginnen eerst met het behandelen van de datum en tijd gerelateerde functies die je in een macro kunt gebruiken. Dat zijn er veel. In de praktijk zijn er echter veel die je niet zult gebruiken. In dit artikel behandel geef ik een uitgebreide beschrijving van de functies die ik in de praktijk vaak gebruik.

Date

Deze functie geeft je de systeemdatum terug. Die kun je bijvoorbeeld opslaan in een variabele voor verdere bewerking. Hieronder een voorbeeld van hoe je deze functie kunt gebruiken in een code en wat de uitkomst is van de msgbox.

Sub datumophalen()

    datum = Date
    MsgBox datum

End Sub

Now

Deze functie lijkt op de date functie echter deze geeft naast de systeemdatum ook de systeemtijd terug.

Sub datumPlusTijdophalen()

    datum = Now
    MsgBox datum

End Sub

Verschillende functies voor eenheden in datums en tijd

Er zijn een veelfuncties waarbij je de eenheden datum en tijd kunt bepalen aan de hand van een datum die je meegeeft. Let erop dat het datatype van de output in dit geval geen ‘date’ is maar een ‘integer’ omdat het gaat om één getal:

• Year functie – geeft een jaartal terug
• Month functie – geeft een maandnummer terug
• Weekday functie – geeft de dag van de week terug 

Uit het hele rijtje is Weekday de enige functie die nog verdere uitleg behoeft. Ik heb dit artikel namelijk geschreven op een donderdag. De uitkomst van de onderstaande code is een msgbox die het cijfer 5 teruggeeft. Dit komt omdat deze functie de zondag als dag één van de week ziet.

Sub Weekdagophalen()

    weekdag = Weekday(Date)
    MsgBox weekdag

End Sub

• Day functie – geeft de dag van de maand terug
• Hour functie – geeft het uur van de dag terug
• Minute functie – geeft de minuut binnen het uur terug
• Second functie – geeft de seconde binnen de minuut terug

Dateserial functie

Dit is de functie die ik in de praktijk het meest toepas. Je kunt met deze functie apart de dag, maand en jaar als input meegeven om als output een datum te genereren. Die input kan bestaan uit harde getallen die je meegeeft maar je kunt hier ook variabelen meegeven. In de onderstaande afbeelding kun je zien dat je dus eerst het jaar dient mee te geven, dan de maand en tenslotte de dag.

Deze functie heeft ook een mooie oplossing om de laatste dag van een bepaalde maand te vinden. Het is namelijk zo dat niet elke maand evenveel dagen heeft. Door bij de maand één maand op te tellen en dan bij dag het getal nul in te vullen schakelt hij terug naar de laatste dag van de maand ervoor.

Sub DeDateSerialFunctie()

    datum = DateSerial(2022, 3, 0)
    MsgBox datum

End Sub

De bovenstaande code geeft dus een msgbox die de datum 28-02-2022 laat zien.

Voorbeeld van problemen met het uitlezen van datums

In de bovenstaande voorbeelden die ik gebruikt heb om te laten zien hoe de verschillende datum en tijd functies werken maakte ik vooral gebruik van de date functie om een datum mee te geven. In dit geval werken de functies altijd goed omdat de date functie een goede datum van het juiste type genereert. Het kan echter ook zijn dat je met datums moet werken uit een bepaald systeem die anders geformatteerd staan dan een datum type. Stel bijvoorbeeld dat het systeem een datum als tekst meegeeft en daarin de notatie ‘m/d/yyyy’ gebruikt. Als je dit in een variabele stopt zul je zien dat VBA de data herkent als Variant/String en dat je deze datum dus ook niet kunt bewerken.

In dit geval kan de hierboven beschreven Dateserial functie echt een uitkomst bieden. Ik zal je een trucje laten zien waarbij ik gebruik maak van een string functie om alsnog een datum te kunnen bepalen aan de hand van deze systeemoutput.

Sub DatumIssue()

    datum = ThisWorkbook.Sheets("Blad1").Range("B2")

    If InStr(Left(datum, 2), "/") = 0 Then
        maand = Left(datum, 2)
        If InStr(Mid(datum, 4, 2), "/") = 0 Then
            dag = Mid(datum, 4, 2)
        Else
            dag = Mid(datum, 4, 1)
        End If
    Else
        maand = Left(datum, 1)
        If InStr(Mid(datum, 3, 2), "/") = 0 Then
            dag = Mid(datum, 3, 2)
        Else
            dag = Mid(datum, 3, 1)
        End If
    End If
    
    jaar = Right(datum, 2)
    
    datum = DateSerial(jaar, maand, dag)
    MsgBox datum

End Sub

De uitdaging met deze datum in tekst vorm is dat de dag en de maand uit één of twee cijfers kunnen bestaan. Wat ik hierboven heb gedaan is met behulp van de LEFT, MID en RIGHT functies gekeken of een deel van de tekst een slash bevat of niet. Dan kun je bepalen of een dag of maand uit één of twee cijfers bestaat. Vervolgens heb ik de dag, maand en jaar afzonderlijk opgeslagen als het type ‘integer’ in een variabele. Tenslotte kun je deze gegevens als input weer gebruiken in de Dateserial functie die deze data weer samen brengt tot een datum.

Formatteren van datums

Indien je de formattering van een datum wilt veranderen kun je dit doen met de format functie.

Sub Datumformatteren()

    datum = Format(Date, "D-M-YYYY")
    MsgBox datum

End Sub

Je hebt veel verschillende formatteringsopties om jouw datum vorm te geven zoals jij hem wilt zien. Op deze pagina kun je een lijst vinden met alle datum en tijd eenheden die je kunt toepassen binnen je format. Je kunt met de format functie ook gemakkelijk de dag, maand en jaar ophalen net zoals wij eerder in dit artikel hebben gedaan met behulp van de Day, Month en Year functie. Zo haal je bijvoorbeeld de dag uit een datum.

Sub Datumformatteren()

    dag = Format(Date, "D")
    MsgBox dag

End Sub

Ik hoop dat je nu een goed beeld hebt over hoe om te gaan met datums en tijd binnen VBA. Mocht je toch nog een vraag hebben. Laat het weten in de reacties!

Converteren van datums

Excel is behoorlijk krachtig als het gaat om het herkennen van datums in stringformaat. Dankzij zijn geavanceerde functies en intelligentie kan het programma vaak een tekststring herkennen die een datum voorstelt en deze automatisch converteren naar het standaard datumformaat.

Wanneer je bijvoorbeeld een string als “21 december 2022” invoert in een Excel-cel, merk je dat Excel het vaak automatisch herkent als een datum. Afhankelijk van de instellingen van je regionale systeem en Excel, zal het programma dit omzetten en weergeven in een specifiek formaat, bijvoorbeeld “21-12-2022”. Excel’s vermogen om een breed scala aan datumformaten te herkennen, zoals “21/12/2022”, “December 21, 2022”, “21 Dec 2022” en vele anderen, maakt het een flexibel en krachtig hulpmiddel.

Met behulp van de Isdate functie kun je tevens controleren of Excel in een bepaalde string een datum herkent. De output van deze functie is een boolean dus TRUE of FALSE