Voor rapportages is het nog wel eens handig om te bepalen hoeveel dagen er zitten tussen een begin- en einddatum. In Excel is dat heel eenvoudig te berekenen door deze datums van elkaar af te trekken. In Access gaat dat op dezelfde manier.
Maar als je wilt weten hoeveel werkdagen er tussen zitten, wordt het wat lastiger. In Excel gaat dat nog vrij eenvoudig met behulp van de ingebouwde functie NETTO.WERKDAGEN, maar in Access komt er wat meer werk om de hoek kijken.
Hieronder een uitleg over de werkwijze in Excel en een voorbeeld hoe de berekening in Access kan worden uitgevoerd.
Dagen verschil in Excel
In het Voorbeeldbestand ziet u op het tabblad Kalender van de Excelsheet Werkdagen.xlsx een Excel-tabel met alle dagen van 2019. In de tweede kolom van die tabel (met de naam tblKalender) wordt het verschil bepaald tussen die dagen en een vaste begindatum (Refdatum). Simpel door ze van elkaar af te trekken via de formule =[@Datum]-RefDatum
NB alle datums in de voorbeelden hebben de volgende celopmaak meegekregen: ddd d mmmm jjjj (zie ook het artikel ginfo.nl/datums-data-dates).
Dus de cel uit de kolom Datum in dezelfde rij (vandaar de @) minus de referentiedatum (in dit geval de inhoud van cel C2).
Een negatief resultaat wil zeggen, dat de datum vóór de referentiedatum ligt.
NB de kleuren in de datum-kolom worden automatisch gegenereerd door middel van Voorwaardelijke opmaak.
Moet de dag zelf ook meetellen dan moet de berekening iets aangepast worden: =[@Datum]-RefDatum+ALS([@Datum]>=RefDatum;1;-1)
Als de dag op of na de referentiedatum ligt, dan moet het resultaat 1 groter worden, anders 1 meer negatief (zie kolom Verschil2).
Zonder weekenden in Excel
Maar als we willen weten hoeveel werkdagen er tussen 2 datums zitten, dan volstaat zo’n simpele berekening niet; zaterdagen en zondagen mogen niet meetellen.
Gelukkig heeft Excel daar een standaard-functie voor: NETTO.WERKDAGEN.
Geef je maar 2 parameters op (de begin- en einddatum), dan bepaalt deze functie het verschil in dagen, exclusief de weekenden.
NB ook hier telt de dag zelf mee; zie in het voorbeeld hierboven het resultaat bij 9 januari.
Echte werkdagen in Excel
Met echte werkdagen bedoelen we dat feestdagen/vrije dagen niet mee mogen tellen.
In het tabblad Feestdagen van het Excel-Voorbeeldbestand is daartoe een overzicht opgenomen met alle mogelijke feestdagen.
Waarschijnlijk zijn dit voor u niet allemaal vrije dagen (helaas?); in de praktijk zult u het overzicht dus moeten aanpassen.
De gegevens zijn opgenomen in de Excel-tabel met de naam tblFeestdagen.
De formule voor de werkdagen is in kolom F uitgebreid met een derde parameter, de verwijzing naar de feestdagen die, net als de weekenden, niet meegerekend moeten worden (alle dagen uit de kolom Datum van de tabel tblFeestdagen).
Dagen verschil in Access
In het Voorbeeldbestand zit ook een Access-database (Werkdagen.accdb). Deze heeft de volgende onderdelen: 2 tabellen (met dezelfde feestdagen en kalender als in de Excel-sheet), een query (VerschilBepalen) waarmee de verschillen tussen begin- en einddatums bepaald worden en een Module1, die een eigen functie bevat waarmee we het verschil in werkdagen kunnen bepalen.
Het eerste stuk van de query ziet er als volgt uit:
Voor alle datums uit de Access-tabel tblKalender wordt het verschil bepaald met een referentiedatum.
Deze referentie ligt nergens vast. Access kent het veld tussen rechte haken met de naam Wat is de referentiedatum? niet.
Bij de weergave van de query zal dan ook een pop-up komen, waarin gevraagd wordt om de inhoud van de parameter op te geven.
NB met de functie CDate zorgen we er voor, dat Access ‘weet’ dat de parameter een datum bevat.
Zonder weekenden in Access
Zoals gezegd kent Access geen functie om werkdagen te bepalen; die moeten we dus zelf programmeren.
De eerste regel definieert de functie met de naam Werkdagen. De functie kent 3 parameters: de eerste is de begindatum en de tweede de einddatum. De derde parameter is optioneel en kan alleen de waarde WAAR of ONWAAR bevatten (Boolean); moet er wel of niet met feestdagen rekening worden gehouden.
Na wat commentaarregels (groene tekst) krijgt de routine de opdracht om bij een mogelijk optredende fout naar het gedeelte te gaan dat begint met Err_Werkdagen.
Met het commando Dim worden diverse variabelen gedefinieerd, die we hierna nodig hebben.
De variabele Start wordt gevuld met het datum-gedeelte van de parameter StartDatum; iets vergelijkbaars gebeurt met de variabele Eind.
In de variabele StartKleinerEind wordt vastgelegd of de variabele Start kleiner of gelijk is aan Eind (WAAR of ONWAAR).
Wanneer de parameter Feestdagen de waarde WAAR bevat, dan worden alle datums uit de tabel tblFeestdagen in de variabele rst gestopt.
Nu kunnen we gaan rekenen:
De variabele intCount wordt op 0 gezet.
In het geval Start kleiner of gelijk is aan Eind dan wordt de eerste Do While-loop doorlopen; als dat niet zo is, de tweede.
Als binnen de loop de variabele Start geen zondag of zaterdag is dan wordt de variabele intCount met 1 opgehoogd.
Moet er rekening gehouden worden met feestdagen dan wordt er via de opdracht FirstFind in de variabele rst gekeken of Start daarin voorkomt.
LET OP Access verwacht #’s rond de datum en de FirstFind-opdracht werkt alleen goed als de datum in Amerikaans formaat wordt meegegeven.
Als de datum Start voor komt in rst (Not rst.NoMatch) dan moet de teller weer verlaagd worden.
De variabele Start wordt opgehoogd en via Loop wordt de berekening opnieuw uitgevoerd met de nieuwe startdatum (als de nieuwe Start tenminste nog kleiner of gelijk is aan Eind)
NB de tweede loop werkt ongeveer hetzelfde maar nu vanuit Start terugtellend naar Eind.
Als de berekening klaar is dan wordt de inhoud van de variabele intCount als resultaat aan de functie teruggeven. Daarbij wordt wel gekeken of het resultaat positief of negatief moet zijn.
Dan is onze functie klaar: Exit Function.
Onderaan staat nog wat de functie moet doen als er een fout optreedt.
Met behulp van deze functie kunnen we de query VerschilBepalen uitbreiden:
NB Omdat de onbekende parameter bij Verschil en ZonderWeekend exact hetzelfde is, zal Access slechts 1 keer vragen om hiervoor een referentiedatum in te voeren.
Het resultaat van de query is nu precies gelijk aan het resultaat van de Excel-functie NETTO.WERKDAGEN, waarbij de derde parameter niet is opgegeven.
Echte werkdagen in Access
Het zal u niet verbazen, dat de uitbreiding van de query met het veld EchteWerkdagen …
… een vergelijkbaar resultaat oplevert als NETTO.WERKDAGEN, rekening houdend met allerlei feestdagen.