Er zijn veel manieren om datums die als tekst zijn opgemaakt in Excel om te zetten in werkelijke datums en deze tutorial is bedoeld om ze allemaal te verkennen.
Wanneer u gegevens in Excel importeert, kunnen deze in veel verschillende vormen voorkomen die Excel niet herkent. Soms, wanneer datums worden geïmporteerd uit een externe bron, worden ze in tekstopmaak weergegeven.
Het is ook belangrijk om te weten dat Excel datumnotaties toepast op basis van uw systeemregio-instellingen. Dus wanneer u gegevens in Excel importeert die uit een ander land komen, herkent Excel ze mogelijk niet en slaat ze op als tekstinvoer.
Als u dit probleem ondervindt, zijn er veel manieren waarop u tekst naar een datum in Excel kunt converteren en we zullen ze allemaal in deze zelfstudie behandelen.
Methoden voor het converteren van tekst naar datum
Als uw werkblad datums bevat die zijn opgemaakt als tekst, in plaats van werkelijke datums, is het niet mogelijk om deze in berekeningen te gebruiken. U moet ze dus terug converteren naar de werkelijke datums.
Als u uw datums links uitgelijnd ziet, betekent dit dat ze zijn opgemaakt als tekst omdat teksten standaard links worden uitgelijnd. En het nummer en de datums zijn altijd rechts uitgelijnd.
Er zijn een paar verschillende manieren waarop u tekst naar een datum in Excel kunt converteren, dit zijn:
- Optie voor foutcontrole
- Excel-functie Tekst naar kolommen
- Zoeken en vervangen
- Plakken speciaal gereedschap
- Excel-formule en functies
Converteer tekst naar datum met behulp van de optie voor foutcontrole
Excel heeft een ingebouwde foutcontrolefunctie die enkele duidelijke fouten in uw gegevens opspoort. Als het een fout vindt, toont het u een kleine groene driehoek (een foutindicator) in de linkerbovenhoek van de cel, die de fout heeft. Als u die cel selecteert, verschijnt er een waarschuwingsbord met een geel uitroepteken. Wanneer u uw cursor over dat teken beweegt, zal Excel u informeren over het mogelijke probleem met die cel.
Als u bijvoorbeeld het jaar in de tweecijferige notatie in uw datum invoert, neemt Excel die datum aan als tekst en slaat deze op als tekst. En als u die cel selecteert, verschijnt er een uitroepteken met een waarschuwing: 'Deze cel bevat een datumreeks die wordt weergegeven met slechts 2 cijfers van het jaar'.
Als uw cellen die foutindicator weergeven, klikt u op het uitroepteken en worden een paar opties weergegeven om datums die als tekst zijn opgemaakt, om te zetten in werkelijke datums. Excel toont u opties om het te converteren naar 19XX of 20XX (19XX voor 1915, 20XX voor 2015). Selecteer de juiste optie.
Vervolgens wordt de tekst geconverteerd naar het juiste datumformaat.
Hoe de optie voor foutcontrole in Excel in te schakelen
Meestal is de optie Foutcontrole standaard ingeschakeld in Excel. Als de functie voor foutcontrole niet voor u werkt, moet u de foutcontrole in Excel inschakelen.
Klik hiervoor op het tabblad 'Bestand' en selecteer 'Opties' in het linkerdeelvenster.
Klik in het venster Excel-opties op 'Formules' in het linkerdeelvenster en schakel in het rechterdeelvenster 'Achtergrondfoutcontrole inschakelen' in het gedeelte Foutcontrole in. En vink de 'Cellen met jaren weergegeven als 2 cijfers' aan in het gedeelte Foutcontroleregels.
Converteer tekst naar datum met behulp van Excel-tekst naar kolomfunctie
Tekst naar kolom is een geweldige functie in Excel waarmee u gegevens in meerdere kolommen kunt splitsen, en het is ook een krachtig hulpmiddel voor het converteren van tekstwaarden naar datumwaarden. Deze methode herkent verschillende gegevensformaten en zet ze om in het juiste datumformaat.
Eenvoudige tekstreeksen converteren naar datums
Laten we zeggen dat uw datums zijn opgemaakt in tekstreeksen zoals deze:
U kunt de wizard Tekst naar kolommen gebruiken om ze snel allemaal opnieuw op te maken naar datums.
Selecteer eerst het bereik van tekstinvoer dat u naar datums wilt converteren. Ga vervolgens naar het tabblad 'Gegevens' in het lint en klik op de optie 'Tekst naar kolommen' in de groep Gegevenshulpmiddelen.
De wizard Tekst naar kolommen verschijnt. Kies in stap 1 van de wizard Tekst naar kolom de optie 'Gescheiden' onder Origineel gegevenstype en klik op 'Volgende'.
Schakel in stap 2 alle vakjes voor scheidingstekens uit en klik op Volgende.
Selecteer in de laatste stap van de wizard 'Datum' onder Kolomgegevensformaat en kies uw datumformaat in de vervolgkeuzelijst naast 'Datums' en klik op de knop 'Voltooien'. In ons geval converteren we de tekstdatums die worden weergegeven als "01 02 1995" (dag maand jaar), dus kiezen we 'DMY' uit de vervolgkeuzelijst 'Datum:'.
Excel converteert nu uw tekstdatums naar werkelijke datums en geeft ze rechts uitgelijnd in de cellen weer.
Opmerking: Voordat u de functie Tekst naar kolom gaat gebruiken, moet u ervoor zorgen dat al uw tekstreeksen een identieke indeling hebben, anders worden teksten niet geconverteerd. Als sommige van uw tekstdatums bijvoorbeeld de notatie maand/dag/jaar (MDY) hebben, terwijl andere de notatie dag/maand/jaar (DMY) zijn, en als u 'DMY' kiest in stap 3, krijgt u onjuiste resultaten. Zoals op onderstaande afbeelding te zien is.
Complexe tekstreeks converteren naar datum
De functie Tekst naar kolommen is handig wanneer u complexe tekstreeksen naar datums wilt converteren. Hiermee kunt u de scheidingstekens gebruiken om te bepalen waar uw gegevens moeten worden gesplitst en weergegeven over 2 of meer kolommen. En combineer de gesplitste delen van datums in de hele datum met behulp van de DATE-functie.
Als uw datums bijvoorbeeld worden weergegeven in meerdelige tekstreeksen, zoals deze:
woensdag 01 februari 2020
01 februari 2020, 16.10 uur
U kunt de wizard Tekst naar kolom gebruiken om de dag-, datum- en tijdinformatie te scheiden die wordt gescheiden door een komma, en deze in meerdere kolommen weer te geven.
Selecteer eerst alle tekstreeksen die u naar datums wilt converteren. Klik op de knop 'Tekst naar kolommen' op het tabblad 'Gegevens'. Kies in stap 1 van de wizard Tekst naar kolom de optie 'Gescheiden' onder Origineel gegevenstype en klik op 'Volgende'.
Selecteer in stap 2 van de wizard de scheidingstekens die uw tekstreeksen bevatten en klik op 'Volgende'. Onze voorbeeldtekstreeksen gescheiden door komma's en spatie - "Maandag 01 februari 2015, 13:00 uur". We moeten 'komma' en 'spaties' kiezen als scheidingstekens om de tekstreeksen in meerdere kolommen te splitsen.
Selecteer in de laatste stap het formaat 'Algemeen' voor alle kolommen in het gedeelte Gegevensvoorbeeld. Geef in het veld 'Bestemming' op waar de kolommen moeten worden ingevoegd, als u dat niet doet, worden de oorspronkelijke gegevens overschreven. Als u een deel van de oorspronkelijke gegevens wilt negeren, klikt u erop in het gedeelte Gegevensvoorbeeld en selecteert u de optie 'Kolom niet importeren (overslaan)'. Klik vervolgens op ‘Voltooien’.
Nu zijn de delen van datums (dagen van de week, maand, jaar, tijd) opgesplitst in kolommen B, C, D, E, F en G.
Combineer vervolgens de datumdelen met behulp van een DATE-formule om de hele datum te krijgen.
De syntaxis van de Excel DATE-functie:
=DATUM(jaar,maand,dag)In ons voorbeeld staan de delen maand, dag en jaar respectievelijk in de kolommen C, D en E.
De DATE-functie herkent alleen cijfers, geen tekst. Aangezien onze maandwaarden in kolom C allemaal tekenreeksen zijn, moeten we ze naar getallen converteren. Om dat te doen, moet u de functie MAAND gebruiken om de naam van de maand te wijzigen in het nummer van de maand.
Gebruik deze MAAND-functie in de DATUM-functie om de naam van een maand om te zetten in een maandnummer:
=MAAND(1&C1)De functie MAAND voegt 1 toe aan cel C2 die de naam van de maand bevat om de naam van de maand om te zetten in het corresponderende maandnummer.
Dit is de DATE-functie die we moeten gebruiken om datumdelen uit verschillende kolommen te combineren:
=DATUM(E1,MAAND(1&C1),D1)
Gebruik nu de vulgreep in de benedenhoek van de formulecel en pas de formule toe op de kolom.
Converteer tekst naar datum met behulp van de methode Zoeken en vervangen
Deze methode gebruikt scheidingstekens om de opmaak van tekst in datums te wijzigen. Als dag, maand en jaar in uw datums worden gescheiden door een ander scheidingsteken dan een streepje (-) of schuine streep (/), zal Excel ze niet herkennen als datums en ze opslaan als tekst.
Gebruik de functie Zoeken en vervangen om dit probleem op te lossen. Door de niet-standaard periodescheidingstekens (.) te wijzigen met schuine strepen (/) of streepje (-), zal Excel de waarden automatisch wijzigen in datums.
Selecteer eerst alle tekstdatums die u naar datums wilt converteren. Klik op het tabblad 'Start' op de knop 'Zoeken en selecteren' in de meest rechtse hoek van het lint en selecteer 'Vervangen'. U kunt ook op drukken Ctrl+H om het dialoogvenster Zoeken en vervangen te openen.
Typ in het dialoogvenster Zoeken en vervangen het scheidingsteken dat uw tekst bevat (in ons geval punt (.) in het veld 'Zoeken naar' en een schuine streep (/) of streepje (-) in het veld 'Vervangen door' Klik op de knop 'Alles vervangen' om de scheidingstekens te vervangen en klik op 'Sluiten' om het venster te sluiten.
Nu herkent Excel dat uw tekstreeksen nu datums zijn en worden ze automatisch opgemaakt als datums. Uw datums worden rechts uitgelijnd zoals hieronder weergegeven.
Converteer tekst naar datum met behulp van speciaal gereedschap Plakken
Een andere snelle en gemakkelijke manier om tekstreeksen naar datums te converteren, is door 0 toe te voegen aan de tekstreeks met de speciale optie Plakken. Door nul toe te voegen aan de waarde, wordt de tekst geconverteerd naar het serienummer van de datum, dat u als datum kunt opmaken.
Selecteer eerst een lege cel en kopieer deze (selecteer deze en druk op Ctrl + C kopiëren).
Selecteer vervolgens de cellen met de tekstdatums die u wilt converteren, klik met de rechtermuisknop en selecteer de optie 'Plakken speciaal'.
Selecteer in het dialoogvenster Plakken speciaal 'Alle' onder het gedeelte Plakken, kies 'Toevoegen' onder het gedeelte Bewerking en klik op 'OK'.
U kunt ook andere rekenkundige bewerkingen uitvoeren door de waarde in de doelcel af te trekken/vermenigvuldigen/delen met de geplakte waarde (zoals cellen vermenigvuldigen met 1 of delen door 1 of nul aftrekken).
Wanneer u 'Toevoegen' selecteert in Bewerking, wordt 'nul' toegevoegd aan alle geselecteerde tekstdatums, aangezien het toevoegen van een '0' de waarden niet verandert, krijgt u het serienummer voor elke datum. Nu hoef je alleen nog maar de opmaak van de cellen te wijzigen.
Selecteer de serienummers en klik op het tabblad 'Home' op de vervolgkeuzelijst 'Nummernotatie' in de groep Nummer. Selecteer de optie 'Korte datum' in de vervolgkeuzelijst.
Zoals u nu kunt zien, zijn de getallen opgemaakt als datums en rechts uitgelijnd.
Converteer tekst naar datum met formules
Er zijn twee functies die voornamelijk worden gebruikt om tekst naar datum te converteren: DATEVALUE en VALUE.
Excel DATEVALUE-functie gebruiken
De Excel DATEVALUE-functie is een van de gemakkelijkste manieren om een datum die als tekst wordt weergegeven, om te zetten in een serienummer van een datum.
De syntaxis van de functie DATEVALUE:
=DATUMWAARDE(datum_tekst)Argument: date_text specificeert de tekstreeks die u wilt verbergen of verwijst naar de cel met tekstdatums.
De Formule:
=DATUMWAARDE(A1)De volgende afbeelding illustreert hoe de DATEVALUE-functie omgaat met een aantal verschillende datumformaten die als tekst zijn opgeslagen.
U hebt de serienummers van de datum, nu moet u het datumformaat op deze nummers toepassen. Selecteer hiervoor de cellen met serienummers, ga vervolgens naar het tabblad 'Home' en kies 'Korte datum' in de vervolgkeuzelijst 'Nummernotatie'.
Nu heb je je opgemaakte datums in kolom C.
Zelfs als er geen jaargedeelte in uw tekstdatum (A8) zit, zal DATUMWAARDE het huidige jaar van de computerklok gebruiken.
De functie DATUMWAARDE converteert alleen de tekstwaarden die op een datum lijken. Het kan geen tekst converteren die tot nu toe op een getal lijkt, en het kan ook geen getalwaarde tot nu toe veranderen, daarvoor heb je de VALUE-functie van Excel nodig.
Excel VALUE-functie gebruiken
De Excel VALUE-functie kan elke tekenreeks die op een datum of getal lijkt, converteren naar een numerieke waarde, dus het is erg handig als het gaat om het converteren van elk getal, niet alleen datums.
De WAARDE-functie:
=WAARDE(tekst)tekst– de tekstreeks die we willen converteren of verwijzen naar de cel die de tekstreeks bevat.
De voorbeeldformule om de tekstdatum om te zetten:
=WAARDE(A1)De onderstaande VALUE-formule kan alle tekstreeksen die op een datum lijken, veranderen in een getal zoals hieronder weergegeven.
De VALUE-functie ondersteunt echter niet alle typen datumwaarden. Als datums bijvoorbeeld decimalen gebruiken (A11), wordt de #WAARDE! fout.
Zodra u het serienummer voor uw datum hebt, moet u de cel opmaken met het serienummer van de datum om het op een datum te laten lijken, zoals we hebben gedaan voor de functie DATUMWAARDE. Om dat te doen, selecteert u de serienummers en selecteert u de optie 'Datum' in het vervolgkeuzemenu 'Nummernotatie' op het tabblad 'Home'.
Dat is alles, dit zijn de 5 verschillende manieren waarop u datums die als tekst zijn opgemaakt, kunt converteren naar datums in Excel.