Generieke formule
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Samenvatting
Als u tekst in een niet-herkende datumnotatie naar een juiste Excel-datum wilt converteren, kunt u de tekst parseren en een juiste datum samenstellen met een formule op basis van verschillende functies: DATUM, LINKS, MIDDEN en RECHTS. In het getoonde voorbeeld is de formule in C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Deze formule extraheert de waarden voor jaar, maand en dag afzonderlijk en gebruikt de functie DATUM om ze samen te voegen tot de datum 24 oktober 2000.
Achtergrond
Wanneer u met gegevens uit een ander systeem werkt, kunt u een situatie tegenkomen waarin datums niet goed worden herkend door Excel, dat de datums in plaats daarvan als tekst behandelt. U kunt bijvoorbeeld de volgende tekstwaarden hebben:
| Tekst | Datum vertegenwoordigd |
| 20001024 | 24 oktober 2000 |
| 20050701 | 1 juli 2011 5 |
| 19980424 | 24 april 1998 |
| 28.02.2014 | 28 februari 2014 |
Wanneer Excel een datumwaarde als tekst heeft geëvalueerd, is een optie om een formule te gebruiken om de tekst in zijn componenten (jaar, maand, dag) te parseren en deze te gebruiken om een datum te maken met de functie DATUM. Zoals hierboven vermeld, raad ik u aan eerst de onderstaande oplossingen te proberen (nul toevoegen en tekst in kolommen gebruiken) voordat u een formule gebruikt. Beide oplossingen zijn sneller en vergen minder inspanning.
Uitleg
De functie DATUM maakt een geldige datum aan met drie argumenten: jaar, maand en dag:
=DATE(year,month,day)
In cel C6 gebruiken we de LEFT-, MID- en RIGHT-functies om elk van deze componenten uit een tekstreeks te extraheren en de resultaten in de DATUM-functie in te voeren:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
De LEFT-functie extraheert de meest linkse 4 karakters voor het jaar, de MID-functie extraheert karakters in posities 5-6 voor de maand, en de RECHTS-functie extraheert de meest rechtse 2 karakters als dag. Elk resultaat wordt rechtstreeks teruggestuurd naar de functie DATUM. Het eindresultaat is een juiste Excel-datum die op elke gewenste manier kan worden opgemaakt.
Deze aanpak kan naar behoefte worden aangepast. De niet-herkende datumnotatie in rij 8 is bijvoorbeeld dd.mm.yyyy en de formule in C8 is:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Lange tekst
Soms heeft u datums in een langere vorm, zoals "11 april 2020 08:43:13" die Excel niet goed herkent. In dit geval kunt u de tekenreeks mogelijk zo aanpassen dat Excel de datum correct kan herkennen met de functie SUBSTITUTE. De onderstaande formule vervangt de tweede instantie van een spatie ("") door een komma en spatie (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Zodra we de komma achter de naam van de maand plaatsen, zal Excel de datum begrijpen, maar het heeft nog steeds een kleine "kick" nodig. Daarom voegen we aan het einde nul toe. De wiskundige bewerking zorgt ervoor dat Excel probeert de tekenreeks naar een getal te converteren. Als dit lukt, resulteert dit in een geldige Excel-datum. Houd er rekening mee dat u mogelijk de datumnotatie moet toepassen om de datum correct weer te geven.
Zonder formules
Probeer een van de onderstaande oplossingen voordat u een formule gebruikt om handmatig een datum uit tekst te ontleden en samen te stellen. De eerste optie gebruikt een wiskundige bewerking om Excel een beetje "aan te duwen" en het te dwingen te proberen de tekst als een getal te evalueren. Omdat Excel-datums in feite cijfers zijn, kan dit vaak de slag slaan. Mogelijk moet u een datumnotatie toepassen als de bewerkingen slagen.
Voeg nul toe om datums vast te leggen
Soms kom je datums tegen in een tekstindeling die Excel zou moeten herkennen. In dit geval kunt u Excel wellicht dwingen de tekstwaarden in datums om te zetten door nul bij de waarde op te tellen. Wanneer u nul toevoegt, probeert Excel tekstwaarden om te zetten in getallen. Omdat datums slechts getallen zijn, is deze truc een geweldige manier om datums om te zetten in een tekstindeling die Excel echt zou moeten begrijpen.
Probeer Plakken speciaal om datums op hun plaats te converteren door nul toe te voegen:
- Voer nul (0) in een ongebruikte cel in en kopieer naar het klembord
- Selecteer de problematische datums
- Plakken speciaal> Waarden> Toevoegen
- Pas een datumnotatie toe (indien nodig)
U kunt ook nul toevoegen in een formule als deze:
=A1+0
waarbij A1 een niet-herkende datum bevat.
Tekst naar kolommen om datums vast te leggen
Een andere manier om Excel datums te laten herkennen, is door de functie Tekst naar kolommen te gebruiken:
Selecteer de kolom met datums en probeer vervolgens Gegevens> Tekst naar kolommen> Vast> Voltooien
Als Excel de datums herkent, worden ze allemaal in één stap opgelost.
