Hoe vindt u snel ontbrekende datums in een lijst in Excel?
In deze handleiding worden verschillende manieren beschreven om ontbrekende datums efficiënt te detecteren en aan te vullen in Excel:
Ontbrekende datums zoeken met Voorwaardelijke opmaak gebruiken
Ontbrekende datums zoeken met een formule
Ontbrekende datums zoeken en invullen met Kutools voor Excel
Gebruik VBA om automatisch ontbrekende datums te identificeren en in te voegen
Markeer ontbrekende datums met behulp van een Draaitabel
Zoek ontbrekende datums met Voorwaardelijke opmaak gebruiken
Een handige manier om gaten in uw datumlijst te identificeren, is door gebruik te maken van Excel’s Voorwaardelijke opmaak. Deze methode markeert visueel de cellen waar een datum ontbreekt in een reeks, zodat discontinuïteiten direct zichtbaar zijn.
1. Selecteer het bereik met uw datums en ga naar Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel. Zie de schermafbeelding:
2. Kies in het dialoogvenster Nieuwe opmaakregel onder Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt bij Selecteer een regeltype de volgende formule: =A2<>(A1+1) (waarbij A1 de eerste datum is en A2 de volgende datum in uw lijst). Zie schermafbeelding:
3. Klik op de knop Opmaak om het dialoogvenster Celopmaak instellen te openen. Kies op het tabblad Opvulling een kleur om de ontbrekende datums te markeren. Zie schermafbeelding:
4. Klik na het instellen van de opmaak tweemaal op OK om toe te passen. Nu worden cellen waar een datum ontbreekt in de reeks gemarkeerd.
Opmerking: De laatste datum in uw lijst kan ook worden gemarkeerd, omdat deze formule controleert wat er na elke datum komt. Deze methode is vooral handig om grote datasets snel te controleren, maar vult ontbrekende datums niet automatisch in.
Zoek ontbrekende datums met een formule
Een andere praktische aanpak is het gebruik van een Excel-formule om direct gaten in uw tabel te identificeren. Deze methode maakt een nieuwe kolom aan die aangeeft of er een dag ontbreekt na elke datum, wat geschikt is voor het bijhouden van aanwezigheidslijsten, projectplanningen of voorraadregistraties.
Voer in een lege kolom naast uw datumlijst (bijvoorbeeld cel B1 als uw lijst begint bij A1) de volgende formule in:=IF(A2=A1+1,"",„Missing next day"). Druk op Enter en sleep het automatisch vulhendel naar beneden om de formule naast alle datums te kopiëren. Zie schermafbeeldingen:
Waar u „Volgende dag ontbreekt” ziet, weet u meteen dat er een gat in uw lijst zit. Dit is een eenvoudige, visuele manier om ontbrekende datums te herkennen en vooral handig als u de geïdentificeerde gaten wilt filteren of verder wilt verwerken.
Opmerking: Net als bij de vorige methode markeert de formule de rij na de laatste datum (aangezien er geen volgende datum is). U kunt deze rij negeren of verwijderen indien niet nodig.
Zoek en vul ontbrekende datums in met Kutools voor Excel
Voor gebruikers van Kutools voor Excel is er een ingebouwde functie waarmee u snel ontbrekende datums of opeenvolgende nummers kunt vinden en zelfs automatisch invullen. Dit is vooral handig wanneer u niet alleen gaten wilt opsporen, maar uw gegevens ook direct wilt aanvullen voor nauwkeurige berekeningen of audits.
Na gratis installeren vanKutools voor Excel, gaat u als volgt te werk:
1. Selecteer de datumlijst die u wilt analyseren en ga naar Kutools > Invoegen > Ontbrekende reeks vinden. Zie de schermafbeelding:
2. In het dialoogvenster Ontbrekende reeks vinden kunt u kiezen uit verschillende opties, zoals het zoeken naar of invoegen van ontbrekende getallen, markeren of een markeringskolom maken. Zie schermafbeelding:
3. Klik na bevestiging van uw keuze op OK. Er verschijnt een melding met het aantal gevonden ontbrekende datums. Zie de schermafbeelding:
4. Klik op OK om te voltooien. Uw lijst toont nu – of vult zelfs automatisch – de ontbrekende datums in, afhankelijk van de gekozen optie. Deze aanpak is ideaal voor grote datasets en minimaliseert fouten door handmatige invoer of onjuiste formules.
| Voeg ontbrekende Reeksnummer in | Voeg Lege rijen in wanneer ontbrekende Opeenvolgende nummers worden aangetroffen |
 |  |
| Voeg een nieuwe kolom in met de volgende marker voor ontbrekende waarden | Achtergrondkleur invullen |
 |  |
Deze tool kan veel tijd besparen en is vooral nuttig voor financiële overzichten, aanwezigheidsgegevens of scenario’s waarin een continue datumregistratie vereist is. Zorg ervoor dat uw lijst op datum gesorteerd is voor optimale resultaten.
Gebruik VBA om automatisch ontbrekende datums te identificeren en in te voegen
Wanneer u werkt met lange of regelmatig bijgewerkte datumlijsten en het proces volledig wilt automatiseren, kunt u een aangepaste VBA-macro in Excel gebruiken. Deze methode scant uw datumkolom, identificeert ontbrekende datums in de reeks en voegt die direct als nieuwe rijen toe aan uw lijst.
Dit is vooral handig bij grote datasets, terugkerende rapportages of wanneer er regelmatig nieuwe gegevens worden toegevoegd en u de volledigheid zonder handmatige controle wilt waarborgen.
Werkstappen:
- Klik op Ontwikkelaar > Visual Basic om de VBA-editor te openen. Klik in het pop-upvenster Microsoft Visual Basic for Applications op Invoegen > Module en plak vervolgens de volgende code in het modulevenster:
Sub InsertMissingDates()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim currentDate As Date, nextDate As Date
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
i = 2
While i < lastRow
currentDate = ws.Cells(i, 1).Value
nextDate = ws.Cells(i + 1, 1).Value
If nextDate > currentDate + 1 Then
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = currentDate + 1
ws.Cells(i + 1, 1).NumberFormat = "yyyy-mm-dd"
lastRow = lastRow + 1
End If
i = i + 1
Wend
End Sub- Klik op de knop
Uitvoeren (of druk op F5) om de code uit te voeren. De macro controleert kolom A op een lijst met datums en voegt automatisch ontbrekende datums toe als nieuwe rijen.
Uitvoeren (of druk op F5) om de code uit te voeren. De macro controleert kolom A op een lijst met datums en voegt automatisch ontbrekende datums toe als nieuwe rijen.Praktische tips en opmerkingen:
– Zorg ervoor dat uw datums oplopend zijn gesorteerd voordat u de macro uitvoert.
– De macro voegt ontbrekende datums in als nieuwe rijen; maak daarom een back-up van uw gegevens of test deze eerst op een kopie.
– Als uw datums niet in kolom A staan, pas dan ws.Cells(i,1) aan naar het juiste kolomnummer.
– Bij zeer grote datasets kan de macro enkele ogenblikken nodig hebben om te voltooien.
– Als u een foutmelding krijgt, controleer dan of alle cellen in uw datumkolom daadwerkelijk datumwaarden bevatten.
Markeer ontbrekende datums met behulp van een Draaitabel
Als u liever geen formules of code gebruikt, kunt u de ingebouwde draaitabel-functie van Excel gebruiken om uw werkelijke datumlijst visueel te vergelijken met een volledige verwachte reeks. Deze methode is bijzonder geschikt voor het analyseren of controleren van aanwezigheidslijsten, transacties of dagelijkse registraties waarbij elke datum binnen een bepaalde periode zou moeten voorkomen.
Werkstappen:
- Maak eerst een hulpkolom met een volledige reeks verwachte datums die uw begin- en einddatum bestrijken. Voer de eerste datum in een cel in (bijvoorbeeld D2) en sleep het vulhendel naar beneden om automatisch datums te genereren tot uw bereik compleet is.
- Kopieer zowel uw oorspronkelijke lijst met datums als uw nieuwe hulpdatumlijst naar een nieuw werkblad en plaats ze onder elkaar in één kolom (bijvoorbeeld kolom E).
- Selecteer de gecombineerde lijst en ga naar Invoegen > PivotTable. Stel in het dialoogvenster het tabellenbereik in en kies een nieuw werkblad voor de uitvoer.
- Sleep in de Draaitabel-veldlijst het datumveld naar het Rijen-gebied en nogmaals naar het Waarden-gebied, en stel de aggregatie in op Aantal. Datums die slechts één keer voorkomen in de Aantal-kolom, geven ontbrekende datums aan (dat wil zeggen: datums die wel in de volledige reeks horen, maar niet in uw werkelijke gegevens voorkomen).
Tips:
– Deze methode werkt het beste bij het controleren van ontbrekende items over langere perioden.
– Zorg ervoor dat uw datumlijsten geen duplicaten bevatten voor optimale resultaten.
– U kunt filteren of markeren in de draaitabel om snel ontbrekende datums te zien.
– Gevorderde gebruikers kunnen deze methode combineren met voorwaardelijke opmaak voor extra zichtbaarheid.
Voordelen: Gemakkelijk te visualiseren; geen formules of VBA vereist; ideaal voor rapportage.
Nadelen: Vult ontbrekende datums niet automatisch in, maar markeert wel welke ontbreken.
Demo: Zoek en voeg ontbrekende datums in een lijst in
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins