Voorspel je business: werken met datums in Excel

Lees ook:

– Nieuwe mogelijkheden in Excel (inclusief 6 Power Pivot tips)

– 10 Excel wetten: het verschil of Excel voor jou of jij voor Excel werkt

Het berekenen van datums in de toekomst

Voor mij ben je heel knap als je het model in afbeelding 1 kunt lezen, maar het enige wat ik daarin zie is het jaartal. Maar dat is dan wel een duidelijke aanduiding dat ook in dit model de periode een rol speelt. 

In dit artikel ga ik het hebben over het berekenen van datums in de toekomst. Wil je jouw verwachting van de toekomst tonen, dan heb je de datums van die toekomst nodig. In dit artikel wordt op de traditionele manier de datums berekend en daarnaast doe ik hetzelfde, maar dan door middel van Power Query.

Wil je meer weten over waar je Power Query in Excel kunt vinden, dan vind je dit op deze webpagina.

Voor diegene die het niet weten, in Excel, onder de motorkap, is iedere datum een getal. Dat is best slim bedacht, want daardoor kan je met datums rekenen. In beide voorbeelden maken we hiervan gebruik. In Excel voor Windows is de eerste dag 1 januari 1900. 2 Januari 1900 is dag 2 en zo heeft iedere dag daarna een opvolgend nummer. Zo heeft 3 juli 2017 het getal 42919 gekregen. Inderdaad, zoveel dagen zijn er verstreken sinds de eerste dag. 

Een andere begindatum in Mac en Windows

Minder slim bedacht is dat de versie van Excel voor Mac een andere begindatum heeft: 1 januari 1904. Een klein, maar gevoelig verschil. Je kunt Excel voor Windows ‘dwingen’ de datum systematiek van Mac te volgen, door in de opties van Excel een vinkje te zetten voor ‘1904-datumsysteem gebruiken’ (zie afbeelding 2), maar het blijft onhandig.

Wat je wilt is een datum tabel in Excel die vanaf de huidige dag 10 jaar vooruit gaat. Daarmee verschuift de datum tabel in tijd, want morgen valt de datum van vandaag eruit en komt er aan het eind van de tabel de volgende toekomstige datum erbij. Een kleine analyse levert op dat je daar 3650 rijen voor nodig hebt, indien je voor het gemak de extra dagen van de schrikkeljaren niet mee telt.

In Excel maak je het op deze manier. In cel A1 komt de kopregel: datum. In Cel A2 typ je een formule, die altijd de datum van vandaag teruggeeft: =VANDAAG(). In cel A3 komt de volgende formule =A2+1. Kijk je naar de formules, dan ziet het resultaat eruit zoals in afbeelding 3. 

De volgende stap is dat je de formule in de cel A3 kopieert naar het einde van het bereik. Dat kun je natuurlijk handmatig doen, maar je kunt ook Excel hiervoor inschakelen. Daar ik van nature lui ben, kies ik voor de laatste optie. Je begint met de formule in cel A3 te kopiëren (tabblad Start, sectie Klembord, de knop Kopiëren). Zie je de bekende stippellijn om deze cel, klik dan in het naamvak (het vak boven cel A1). De cel verwijzing wordt blauw en je typt de rechter onder positie van het bereik: A3652 (zie afbeelding 4). Hou de SHIFT-toets ingedrukt -daarmee activeer je de selectie- en druk eenmaal op de Enter toets. Vervolgens laat je de SHIFT-toets los en druk je nog eenmaal op de Enter toets. En daar staat jouw datumlijst. 

Open je dit model, dan wordt eerst cel A2 en vervolgens de gehele datum lijst herrekent en ben je (letterlijk) up-to-date. Toch heeft deze methode een nadeel, iedere dag is namelijk een berekening. Dat betekent meer formules toevoegen of weghalen als je meer flexibiliteit wilt hebben binnen de gewenste periode.

Ben je toe aan meer flexibiliteit, dan stap je over naar Power Query of Ophalen en transformeren, zoals het in Excel 2016 heet. 

Uitwerking van deze methode in Excel 2016 

Ik werk deze methode uit in Excel 2016, maar de stappen zijn gelijk in alle andere versies van Excel / Power Query. Je begint met een nieuw tabblad. Daar maak je de tabel, die je in afbeelding 5 ziet. In cel A2 wordt opnieuw gebruik gemaakt van de functie =VANDAAG() en in cel B2 staat het aantal dagen, dat je vooruit wilt prognosticeren. Zorg dat de actieve cel in de tabel staat en kies in het tabblad Gegevens, sectie ophalen en transformeren voor de knop “uit tabel” (afbeelding 6). Klik op OK in het venster Tabel maken. Het venster Query-editor wordt geopend en de tabel verschijnt (afbeelding 7).

Een datum is een nummer 

Nu ga je gebruik maken van de wetenschap dat een datum een nummer is. In de koprij van de kolom Huidige datum staat een icoon met het data type. Power Query heeft gedetecteerd dat het een datum is en op zich is dat correct. Maar jij klikt op het icoontje en je kiest uit het menu ‘Geheel getal’. Power Query geeft een melding over de aanpassing van de laatste stap en daarop antwoord je met OK. De tabel ziet eruit als in afbeelding 8. 

Kies nu in het tabblad Kolom toevoegen, sectie Algemeen voor de knop Aangepaste kolom. In het venster ‘Aangepaste kolom toevoegen’ type je de code, die je ziet in afbeelding 9. Let wel op: de code is hoofdletter gevoelig! Heb je vervolgens op de OK-knop geklikt, dan krijg je een nieuwe kolom in de tabel. Het filter icoon in de koprij is een uitklap icoon geworden (afbeelding 10). Je klikt op dit icoon en kiest uit het menu de optie ‘Uitvouwen naar nieuwe rijen’. De basis van de datum tabel staat op het scherm.

Je zet de volgende stappen om deze tabel in Excel te krijgen. Je selecteert de kolommen Huidige datum en Aantal dagen. In het tabblad Start, sectie Kolommen beheren, kies je voor de knop Kolommen verwijderen.  Nu klik je weer op de knop van het data type in de koprij van de kolom Datum. Hier kies je weer voor Datum. De kolom is klaar om terug te keren naar Excel (afbeelding 11). Nu kies je in het tabblad Start, sectie Sluiten voor de knop Sluiten en laden et voilà, in een nieuw tabblad staat de gewenste tabel.

In tegenstelling tot de eerste (Excel) manier van het maken van een datum tabel, bevat deze tabel geen berekening. Wil je de tabel weer opnieuw genereren, dan kies je voor het tabblad Gegevens, sectie Verbindingen, de knop Alles vernieuwen.

In een ander artikel ga ik in hoe je in Power Query gegevens aan de geëxtrapoleerde datum tabel kunt koppelen. De Excel methode heeft als voordeel dat de datum lijst automatisch wordt berekend, maar mist flexibiliteit. De methode met Power Query is veel flexibeler, maar moet per keer worden gegenereerd. De keuze is aan jou.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen.

Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.