Wil je in Excel berekenen hoeveel dagen iets is verlopen?
Er zijn verschillende manieren hoe je dit kunt doen, en het is maar net wat je behoeften zijn en welke structuur jouw voorkeur heeft. In dit artikel heb ik daarom meerdere voorbeelden uitgewerkt. Kies vooral diegene die het beste past bij jouw situatie.
Voorbeeld 1
We beginnen met een simpel voorbeeld zodat je de basis begrijpt.
In dit voorbeeld staat in cel A2 de datum waarop de factuur is verzonden. Stel de vervaldatum is 30 dagen later en deze willen we in cel B2 tonen. Dan hoef je simpelweg alleen de formule =A2+30 in te voeren en Excel zet automatisch de vervaldatum 30 dagen later.
Een volgende stap zou kunnen zijn om in cel C2 te berekenen hoeveel dagen er nog over zijn. Hier kunnen we de functie VANDAAG voor gebruiken. Deze functie haalt de huidige datum op en heeft geen argumenten.
De formule wordt dan =B2-VANDAAG()
Het is hierbij van belang dat je de getalnotatie van cel C2 op ‘getal‘ instelt, omdat Excel soms de getalnotatie automatisch op ‘datum‘ zet als je een berekening met datums uitvoert. Dit kan ervoor zorgen dat je niet het aantal resterende dagen ziet.
Als de waarde positief is, dan zijn er nog dagen over. Ik schrijf dit artikel echter op 4 februari, waardoor de termijn is verstreken en er een negatieve waarde wordt getoond.
Vervolgens kun je eventueel nog met de ALS functie in kolom D de status bepalen.
Hiermee kun je een logische test uitvoeren en bepalen wat Excel moet tonen indien hier wel of niet aan wordt voldaan.
Een simpel voorbeeld is de formule =ALS(C2>0;"Open";"Te laat")
- Excel controleert eerst of de inhoud van del C2 groter is dan 0. Met andere woorden of er nog dagen over zijn.
- Zo ja, dan retourneert Excel de status Open.
- En anders retourneert Excel Te laat.
Je kunt eventueel de formule aanpassen zodat je meerdere voorwaarden kunt gebruiken. Dit wordt een geneste ALS functie genoemd.
Hiermee kunnen we bijvoorbeeld een extra status toevoegen voor facturen die vandaag verlopen. Het eerste gedeelte van deze formule blijft hetzelfde als net: =ALS(C2>0;"Open" alleen daarna willen we de volgende logische test maken met Excel. Daarom voegen we nu de ALS functie in bij het volgende argument:
- ALS(C2=0 (dus wanneer er precies 0 dagen over zijn)
- En dan moet Excel de status ‘Vervalt vandaag’ tonen.
ALS(C2=0; “Vervalt vandaag”
- En tenslotte moeten we Excel nog laten weten wat het moet tonen als C2 niet groter is dan 0 en ook niet gelijk is aan 0. Dat is de status ‘Te laat’.
Wanneer je dit allemaal samenvoegt, krijg je de formule =ALS(C2>0; "Open"; ALS(C2=0; "Vervalt vandaag"; "Te laat"))
Als dit te snel voor je gaat, dan raad ik je aan om mijn gratis handleiding over het nesten van functies te bekijken. Hier leg ik stap voor stap uit hoe je functies met elkaar kan combineren, zoals we ook in dit voorbeeld hebben gedaan met twee keer de ALS functie.
Voorbeeld 2
Nu gaan we dezelfde logica toepassen, maar met een iets andere structuur waardoor je het gemakkelijker kunt gebruiken voor het bijhouden van meerdere facturen.
In dit voorbeeld kies ik voor de koppen ‘Factuurdatum’, ‘Betaald?’, ‘Status’ en ‘Aantal dagen over’.
Stap 1
Om het dynamisch te maken voor meerdere facturen, kies ik er voor om dit in een tabel weer te geven.
Hiervoor selecteer ik het bereik en ga in het lint naar het tabblad Invoegen en klik op ‘Tabel‘. Je kunt ook de sneltoets Ctrl + L gebruiken.
Stap 2
In kolom A kun je zelf de factuurdatum invoeren waarop je de factuur hebt verzonden.
In kolom B kun je invoeren of de factuur betaald is of niet. Je zou hier gebruik van gegevensvalidatie kunnen maken, zodat je een keuzelijst krijgt met alleen ‘Ja’ en ‘Nee’.
Selecteer in dat geval de cellen in kolom B (B2:B11 in dit voorbeeld) en ga in het lint naar het tabblad Gegevens en klik op de knop voor Gegevensvalidatie:
In het dialoogvenster dat wordt geopend kun je vervolgens meerdere dingen instellen. Het belangrijkste is dat je kiest voor ‘Lijst’ en bij bron de opties invoert waaruit de lijst moet bestaan.
Stap 3
Vervolgens gaan we eerst in kolom D het aantal dagen dat over is berekenen.
Dit kan met een hele eenvoudige som: de begindatum + het betalingstermijn – de de huidige datum. Stel we willen een betalingstermijn van 14 dagen hanteren. Dan krijg je de volgende formule:
=A2+14-VANDAAG()
We krijgen dan in eerste instantie met een aantal dingen te maken die we niet willen:
Het eerste probleem is, net zoals bij voorbeeld 1, dat Excel het resultaat als datum weergeeft. Dit kun je oplossen door de cellen op te maken als ‘Getal’ en vervolgens het aantal decimalen op 0 te zetten.
Het probleem met de overige cellen is dat wanneer de cel met de factuurdatum in kolom A leeg is, de som neerkomt op 0+14-VANDAAG()
Het is belangrijk om te weten dat Excel datumwaarden intern opslaat als getallen. (1 januari 1900 is bijvoorbeeld 1, 2 febuari 1900 is 2, etc).
Het getal dat de functie VANDAAG oplevert is een enorm getal. 4 februari 2025 (de dag dat ik dit artikel maak) is bijvoorbeeld 45692. Waardoor je dus steeds een enorm negatief getal ziet als er geen datum is ingevoerd in kolom A.
Je kunt dit oplossen door eerst te controleren of cel A2 gevuld is met behulp van de ALS functie. Hiervoor maken we ook gebruik van de functie ISLEEG als argument.
- ISLEEG(A2) controleer tof cel A2 leeg is
- Als dat zo is, willen we dat Excel een lege cel toont, dus typen we alleen “”
- Als er wel een datum staat in cel A2, dan willen we dat de berekeningen die we in eerste instantie hadden gemaakt wordt uitgevoerd.
Als je dit allemaal samenvoegt, krijg je de formule:
=ALS(ISLEEG(A2);"";A2+14-VANDAAG())
Het resultaat is dat zodra er in kolom A een factuurdatum is ingevoerd, automatisch wordt berekend hoeveel dagen er nog over zijn.
Is er geen factuurdatum ingevoerd, zoals in bovenstaand voorbeeld in de overige cellen, dan blijft kolom D leeg.
Stap 4
In kolom C kunnen we tenslotte de status wederom bepalen met de ALS functie. De formule die we hiervoor in cel C2 invoeren werkt als volgt:
Ik wil bijvoorbeeld dat wanneer in cel B2 (bij ‘Betaald?’) ‘Ja’ is ingevoerd
- Dit gedeelte van de formule wordt dan =ALS(B2=”Ja”;
Vervolgens laat je Excel weten wat het moet retourneren indien deze test WAAR is.
- Kies bijvoorbeeld voor de status “Betaald”.
Daarna willen we dat Excel controleert of het aantal dagen dat over is kleiner is dan 0.
- Dit kan met de logische test =ALS(D2<0;
Indien dit WAAR is, moet Excel bijvoorbeeld de status ‘Verlopen’ tonen.
- Het volgende argument is daarom “Verlopen”;
En tenslotte moeten we bepalen wat Excel moet tonen indien de factuur nog niet is betaald en ook nog niet is verlopen.
- Kies bijvoorbeeld voor de status ‘Binnenkort’.
Wanneer je dit allemaal samenvoegt, krijg je de volgende formule:
=ALS(B2="Ja";"Betaald";ALS(D2<0; "Verlopen";"Binnenkort"))
Om het visueel mooier te maken, geldt hiervoor ook dat het beter is om eerst te controleren of cel A2 een waarde bevat. Hiervoor kun je net zoals net eerst de ALS functie en de ISLEEG functie gebruiken.
De formule wordt in dat geval =ALS(ISLEEG(A2);"";ALS(B2="Ja";"Betaald";ALS(D2<0;"Verlopen";"Binnenkort")))
Je kunt nu op een dynamische manier steeds facturen invoeren en Excel laten controleren of de factuur verlopen is en hoeveel dagen er nog over zijn.
Stap 5 (optioneel)
Het werkt nu in principe prima, maar als ik bijvoorbeeld de factuurdatum wijzig in 1 januari, geeft Excel weer dat er nog -20 dagen over zijn.
Het is optioneel, maar het oogt professioneler als we de formule in kolom D iets aan gaan passen.
We gaan nu ook gebruik maken van de functie OF. Hiermee kun je Excel laten controleren of minimaal één van meerdere voorwaarden waar is.
- We beginnen wederom de formule met =ALS(
Daarna laten we de OF functie controleren of één van deze twee dingen waar is: De factuurdatum in cel A2 leeg is. Hiervoor gebruiken we net zoals in de eerdere formule de ISLEEG functie. En we laten deze functie controleren of de factuur al betaald is door de logische expressie B2=”Ja”.
- Dit resulteert in OF(ISLEEG(A2); B2=”Ja”)
De rest van de formule blijft hetzelfde als hoe het was, maar voor de duidelijkheid zal dit opnieuw worden toegelicht.
Als cel A2 leeg is of als B2 ‘Ja’ is, dan willen we een lege cel tonen.
- Het volgende argument wordt dan alleen twee dubbele aanhalingstekens “”
Als de factuur nog niet betaald is (en in B2 dus geen ‘Ja’ staat) en er wel een datum staat in cel A2, dan willen we weten hoeveel dagen er nog over zijn.
- De som om dit te berekenen blijft A2+14-VANDAAG()
Voeg je dit allemaal samen, dan krijg je de volgende formule: =ALS(OF(ISLEEG(A2);B2="Ja");"";A2+14-VANDAAG())
Als je nu een datum invoert, zoals hierboven, en de status is betaald, dan wordt er niet langer getoond hoeveel dagen er nog over zijn. Is de factuur nog niet betaald, dan wordt de status automatisch verlopen en dan zie je wel hoeveel dagen de factuur al is verlopen.
Nawoord & mijn template
Ik hoop dat je na het lezen van dit artikel een goed beeld hebt gekregen van hoe je in Excel kunt berekenen hoeveel dagen iets is verlopen met behulp van verschillende functies en formules.
Je kunt deze principes toepassen voor allerlei doeleinden. Maar als je, net zoals in de voorbeelden in dit artikel, het wilt gebruiken om je facturen te beheren, dan kan je ook een kijkje nemen naar mijn ‘factuur tracker template’. Dit kost eenmalig €10 en kan je een hoop tijd besparen, omdat je zelf niets hoeft op te zetten.
Wil je meer weten? Klik op de onderstaande knop om mijn template te bekijken:
Wil je liever zelf iets bouwen, omdat mijn template niet helemaal bij jouw situatie past of omdat je het simpelweg leuk vindt om te doen? Dat is natuurlijk helemaal prima. In dat geval hoop ik dat je wat hebt gehad aan de informatie uit dit artikel.
