Hoewel een echte prikklok niet vaak meer wordt gebruikt, wordt bij veel bedrijven nog steeds gebruik gemaakt van een bepaalde manier van tijdregistratie.
Dit kan nodig zijn om binnen het bedrijf een kostenallocatiemodel te voeden of om als externe inhuur verantwoording af te leggen over de in rekening gebrachte uren, enzovoort.
Daarom in dit artikel aandacht voor Excel als hulpmiddel voor urenregistratie; inclusief wat VBA om het gebruik wat makkelijker te maken.
Basis
Het is een goede gewoonte om bij de opbouw van een Excel-systeem een scheiding aan te brengen tussen de diverse onderdelen; in dit geval hebben we dan ook de invoer van de gemaakte uren en de rapportage daarover in aparte tabbladen opgenomen.
In het Voorbeeldbestand is dit verder uitgewerkt.
De registratie en rapportage zijn geen complexe items, maar we kunnen wel diverse handige Excel-trucjes gebruiken om het systeem flexibeler en fraaier te maken.
Laten we de kolommen in het tabblad Invoer eens langs lopen:
- in kolom A komt de datum: in cel A4 staat de eerste datum waarop de registratie is begonnen; in A5 staat de formule =A4+1; in A6 de formule =A5+1 etc.
Het voordeel hiervan is, dat als we met een nieuwe registratie willen beginnen, we alleen de datum in cel A4 hoeven te wijzigen - voor een snel inzicht tonen we in kolom B de dag van de week; niet via een Excel-functie Weekdag (die geeft alleen het volgnummer in de week) maar door opmaak.
De formule in B4 is =A4.
NB De invoer is als tabel in Excel opgezet; dat houdt onder andere in, dat als je zo’n formule in B4 intypt deze automatisch in de rest van de kolom wordt overgenomen (maar wel met een verwijzing naar A5, A6 etc).
Via de celeigenschappen hebben de cellen in kolom B een speciale datumopmaak meegekregen, namelijk dddd. Deze zorgt er voor, dat de datum als volledige weekdag wordt weergegeven.
NB experimenteer met het aantal d’s en kijk wat dit voor invloed heeft op de opmaak. - in kolom C wordt per dag de begintijd ingevoerd
- we gaan er van uit, dat een dag uit 2 blokken bestaat; in D komt dan de eindtijd van blok1, in E een eventueel begin van blok2 en in F een eventueel einde.
De opmaak van de kolommen C, D, E en F is u:mm (dus minstens 1 positie voor het uur en altijd 2 voor de minuten). 
dan wordt het spannender: in kolom G komt een formule, die de gewerkte tijd (nou ja, de aanwezigheid) berekent. Dit gebeurt in 2 gedeeltes: in de eerste Als berekenen we het verschil tussen begin- en eindtijd van het eerste blok (als er nog geen eindtijd is (D4=””) dan maken we dat verschil gelijk aan 0); en we tellen daar het resultaat van de tweede Als bij op, die een eventueel verschil van blok2 bepaalt.
Ook deze kolom krijgt als opmaak u:mm, omdat we natuurlijk het resultaat in uren en minuten willen weten.
Wanneer het resultaat nul is (in het weekend of op andere (nog) niet gewerkte dagen), willen we in kolom G niets zien: de opmaak is dan ook uitgebreid met een extra voorwaarde:
- Kolom H bevat ook het aantal uren, maar nu uitgedrukt als een decimaal getal. Om de gehanteerde formule in die kolom te begrijpen, moet je weten, dat Excel een tijd als een deel van een gehele dag opslaat: 24 uur is het getal 1, 12 uur is 1/2, 6 uur wordt vastgelegd als 1/4 etc.
Andersom: willen we de tijd uit kolom G (die dus als fractie van een hele dag is opgeslagen) weergeven als uren, dan moeten we die tijd met 24 vermenigvuldigen. Aangezien we met een Excel-tabel werken, wordt dit via een zogenaamde gestructureerde verwijzing in de formule weergegeven:
(Op tabellen en gestructureerde verwijzingen zal ik een andere keer terugkomen) - in kolom I kunnen (relevante) opmerkingen, die de registratie verduidelijken, worden opgenomen.
Rapportage
De maandrapportage van de tijdsbesteding is in het Voorbeeldbestand in het tabblad MndOvz opgenomen.
Door de datum van de eerste van een maand op te geven, worden op deze pagina de bij die maand behorende gegevens uit de database opgehaald. Hierbij wordt een alternatief voor verticaal zoeken gebruikt.
- van de bovenste 6 rijen is alleen cel D5 echt van belang: deze geeft aan van welke maand de gegevens worden weergegeven (de eerste van de maand moet worden ingetikt; de opmaak laat alleen maar maand en jaar zien).
De rest is verfraaiing/toelichting. - in kolom B staan vanaf regel 11 de dagen van de betreffende maand weergegeven; althans het volgnummer van de dagen.
- in kolom C staat de werkelijke dag, via de formule =$D$5+B11-1 (dus bij de eerste van de maand (D5) wordt het volgnummer opgeteld; omdat we dan altijd 1 dag te ver uitkomen trekken we er nog 1 vanaf).
NB1 Kolom C is niet verborgen, maar via groepering ‘dichtgeklapt’. Klik op het +-teken boven D om kolom C zichtbaar te maken. Groeperen zit in de menutab Gegevens, in het blok Overzicht.
NB2 aangezien we natuurlijk alleen maar datums uit de betreffende maand willen zien (en bijvoorbeeld geen 31 april) is de formule vanaf C12 iets ingewikkelder. - laten we dan eens kijken wat de formule in D11 doet:
=ALS.FOUT(INDEX(Uren;VERGELIJKEN($C11;Uren[Datum];0);D$10);””)
Uren is de naam van de tabel uit het invoerblad.
Uren[Datum] is de datum-kolom in die tabel.
VERGELIJKEN($C11;Uren[Datum];0) kijkt op welke plaats de waarde uit C11 (in dit geval dus 1 april) in die kolom staat; de 0 zorgt er voor, dat Excel de waarde zoekt, onafhankelijk in welke volgorde die ook zouden staan (een exacte match dus).
INDEX(Uren; ‘plaats van datum’ ;D$10) geeft de waarde van díe cel in de tabel Uren, die in de rij ‘plaats van datum’ staat en in de kolom, die overeenkomt met de waarde in cel D10.
ALS.FOUT geeft een lege waarde (“”) als één van de formules INDEX of VERGELIJKEN een fout oplevert (bijvoorbeeld als de datum uit C11 niet in de tabel Uren voorkomt).
NB in het overzicht staat ook een dichtgeklapte rij 10; daar staat in welke kolom Index moet zoeken. - de overige cellen in het overzicht zijn op eenzelfde manier opgezet
- onderaan staat nog een totaaltelling: in cel I42 worden de ‘decimale’ uren opgeteld. In H42 de ‘normale’ uren en minuten; wanneer het aantal uren boven de 24 komt, zal Excel standaard weer opnieuw bij 0 beginnen. Willen we die uren boven de 24 zichtbaar maken dan dienen we de betreffende cel een andere opmaak mee te geven:
. Let op de vierkante haken!
VBA
Om het gebruik van het spreadsheet wat te vergemakkelijken is nog een VBA-routine toegevoegd.
De bedoeling van de routine is om bij het openen van het bestand de cursor op de juiste plaats te hebben staan om snel nieuwe invoer te kunnen doen.
De VBA-routine gaat automatisch naar het Invoer-blad en zoekt de regel op net onder de laatste invoer.
VBA-routines worden veelal opgeslagen in zogenaamde Modules; omdat deze routine direct actief moet worden wanneer de werkmap (in het Engels Workbook) wordt geopend staat deze routine in de map ThisWorkbook en heeft de naam Workbook_Open gekregen.
Laten we de routine even stapsgewijs doorlopen:
- open het Voorbeeldbestand
- ga naar Visual Basic (bijvoorbeeld via de toetscombinatie Alt-F11)
- dubbelklik op de map ThisWorkbook
- de routine begint met wat toelichtende commentaarregels (de groene regels na de apostrof)
dan worden 2 variabelen gedeclareerd, die we hierna nodig hebben (strGebrNaam en strDagDeel)- de 2 variabelen worden gevuld; de bedoeling lijkt me duidelijk
- en dan het ‘echte’ werk:
Eerst selecteren we het tabblad (de sheet) Invoer; dan selecteren we in de kop (Header) van de tabel Uren de cel met het woord Datum.
Vervolgens wordt de toets-combinatie Ctrl-Pijl-naar-beneden nagebootst en dus de onderste gevulde datum geselecteerd.
Dan 2 kolommen naar rechts om in de kolom Begin1 te komen, waar we via Ctrl-Pijl-naar-boven de laatst gevulde cel zoeken.
Die is al gevuld, dus selecteren we de cel daaronder.
Als laatste wordt een pop-up op het scherm getoond.
NB vbCrLf is de code, die er voor zorgt dat de volgende tekst, die via het &-teken aan het voorgaande wordt ‘geplakt’, 1 regel lager zal komen (een zogenaamde harde-return). Zie Teksten samenvoegen voor uitleg.
Als de VBA-routine niet duidelijk is, laat dan commentaar of een vraag achter op de website.