1. Home
  2. Artikelen
  3. Voorspel je business: werken met datums in Excel

Voorspel je business: werken met datums in Excel


Forecasting, (revolving) liquiditeitsprognoses, vastgoedportefeuilles, begrotingen en budgetten hebben een ding gemeen. Het gaat over voorspellingen in de toekomst. Daarvoor gebruikt bijna ieder Excel model datums in een of andere vorm.

Voorspel je business: werken met datums in Excel

Lees ook:
Nieuwe mogelijkheden in Excel (inclusief 6 Power Pivot tips)
VBA in Excel is niet dood (5 stappen naar efficiënt proces)
10 Excel wetten: het verschil of Excel voor jou of jij voor Excel werkt

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. 

Sorry, geen artikelen gevonden in deze categorie.

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. 

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).

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. 

Sorry, geen artikelen gevonden in deze categorie.

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.


Gerelateerde artikelen

Vier pijlers voor betere besluiten

29-07-2020 07:46

Hoe zorg je ervoor dat jij en je team komen tot de best mogelijke beslissingen?

5 stappen in Power BI: Stap 2 Modelleren en bereke...

28-07-2020 08:15

Modelleren en berekenen, Power BI en DAX: webinar van Excel MVP Tony de Jonker.

Tips om jouw Excel-model hufterproof te maken

27-07-2020 14:48

Wil je hufterproof Excel-modellen maken dan moet je een andere mindsetting van werken in Excel aanme...

Explosieve kracht in een cel: aggregaat berekening...

27-07-2020 14:38

Data is het nieuwe goud, maar aggregaat berekeningen in Excel laten dat goud glanzen.

Excel: de kunst van rij in kolom berekeningen

27-07-2020 14:24

Ik heb Excel nog nooit op een rekenfoutje betrapt. Het is de onkunde van de gebruiker die zorgt voor...

Hoe laat je Excel leven?

27-07-2020 13:27

Kijk voorbij de dagelijkse eisen en ontdek de onbekende meerwaarde van Excel.

Hoe Power BI je helpt op koers te blijven

27-07-2020 13:12

Hoe Power BI je helpt op koers te blijven

Overstappen van Excel naar Power BI?

27-07-2020 13:05

Power BI is hot. Voor nog geen tientje per gebruiker per maand, state of the art rapportage software...

De zeven pijlers van control voor morgen

23-07-2020 13:29

Wat moet je echt beheersen om als controller mee te kunnen blijven doen? Digitalisering, globaliser...

Hoe krijg je grip op verbonden partijen?

10-07-2020 08:14

Nieuwe methode om tijdig te signaleren hoe ernstig de situatie is: risicogestuurd grip. Een serie...

Veerkracht control, voor echte meerwaarde

10-07-2020 07:58

Als wáre businesspartner van het management biedt de controller veerkracht: wendbaarheid plus weerba...

Overname? Emoties kosten geld

08-07-2020 08:48

"Na de transactie moet er worden gewerkt om de krachten te bundelen." Evert Oosterhuis, partner Inte...