Der er mange måder at konvertere datoer formateret som tekst til faktiske datoer i Excel, og denne tutorial har til formål at udforske dem alle.
Når du importerer data til Excel, kan de komme i mange forskellige former, som Excel ikke genkender. Nogle gange, når datoer importeres fra en ekstern kilde, kommer de i formateret som tekst.
Det er også vigtigt at vide, at Excel anvender datoformater baseret på dine systemområdeindstillinger. Så når du importerer data til Excel, der kom fra et andet land, genkender Excel dem muligvis ikke og gemmer dem som tekstindtastninger.
Hvis du har dette problem, er der mange måder, du kan bruge til at konvertere tekst til en dato i Excel, og vi vil dække dem alle i denne tutorial.
Metoder til konvertering af tekst til dato
Hvis dit regneark indeholder datoer formateret som tekst, i stedet for faktiske datoer, er det ikke muligt at bruge dem i nogen beregninger. Så du skal konvertere dem tilbage til faktiske datoer.
Hvis du ser dine datoer justeret til venstre, betyder det, at de er formateret som tekst, fordi tekster er venstrejusterede som standard. Og antallet og datoerne er altid justeret til højre.
Der er få forskellige måder, du kan konvertere tekst til en dato i Excel, de er:
- Fejlkontrol mulighed
- Excel Tekst til kolonner-funktion
- Find & Erstat
- Indsæt Specialværktøj
- Excel Formel og funktioner
Konverter tekst til dato ved hjælp af fejlkontrol
Excel har en indbygget fejlkontrolfunktion, der opdager nogle åbenlyse fejl i dine data. Hvis den finder en fejl, vil den vise dig en lille grøn trekant (en fejlindikator) i øverste venstre hjørne af cellen, som har fejlen. Hvis du vælger den celle, vil et advarselstegn med et gult udråbstegn dukke op. Når du flytter markøren over det tegn, vil Excel informere dig om det mulige problem med den celle.
For eksempel, når du indtaster året i det tocifrede format i din dato, antager Excel denne dato som en tekst og gemmer den som tekst. Og hvis du vælger den celle, vises et udråbstegn med en advarsel: 'Denne celle indeholder en datostreng repræsenteret med kun 2-cifre i året'.
Hvis dine celler viser den fejlindikator, skal du klikke på udråbstegn, og det vil vise et par muligheder for at konvertere datoer formateret som tekst til faktiske datoer. Excel vil vise dig muligheder for at konvertere det til 19XX eller 20XX (19XX for 1915, 20XX for 2015). Vælg den relevante mulighed.
Derefter vil teksten blive konverteret til korrekt datoformat.
Sådan aktiverer du fejlkontrol i Excel
Normalt er indstillingen Fejlkontrol slået til i Excel som standard. Hvis fejlkontrolfunktionen ikke virker for dig, skal du aktivere fejlkontrol i Excel.
For at gøre dette skal du klikke på fanen 'Filer' og vælge 'Indstillinger' i venstre panel.
I vinduet Excel-indstillinger skal du klikke på 'Formler' i venstre panel, og i panelet til højre skal du aktivere 'Aktiver kontrol af baggrundsfejl' under sektionen Fejltjek. Og afkryds 'Celler, der indeholder år repræsenteret som 2 cifre' i afsnittet om fejlkontrolregler.
Konverter tekst til dato ved hjælp af Excel-tekst til kolonnefunktion
Tekst til kolonne er en fantastisk funktion i Excel, der giver dig mulighed for at opdele data i flere kolonner, og det er også et kraftfuldt værktøj til at konvertere tekstværdier til datoværdier. Denne metode genkender flere forskellige dataformater og konverterer dem til korrekt datoformat.
Konvertering af simple tekststrenge til datoer
Lad os sige, at dine datoer er formateret i tekststrenge som denne:
Du kan bruge guiden Tekst til kolonner til hurtigt at omformatere dem alle tilbage til dato.
Først skal du vælge rækken af tekstindgange, du vil konvertere til datoer. Gå derefter til fanen 'Data' på båndet, og klik på 'Tekst til kolonner'-indstillingen i gruppen Dataværktøjer.
Guiden Tekst til kolonner vises. På trin 1 i guiden Tekst til kolonne skal du vælge indstillingen 'Afgrænset' under Original datatype og klikke på 'Næste'.
I trin 2 skal du fjerne markeringen i alle 'Afgrænsere'-felter og klikke på Næste.
I det sidste trin af guiden skal du vælge 'Dato' under Kolonnedataformat og vælge dit datoformat fra rullelisten ved siden af 'Datoer', og klikke på 'Udfør'-knappen. I vores tilfælde konverterer vi tekstdatoerne repræsenteret som "01 02 1995" (dag måned år), så vi vælger 'DMY' fra rullelisten 'Dato:'.
Nu konverterer Excel dine tekstdatoer til faktiske datoer og viser dem højrejusteret i cellerne.
Bemærk: Før du begynder at bruge Tekst til kolonne-funktionen, skal du sørge for, at alle dine tekststrenge er i et identisk format, ellers bliver tekster ikke konverteret. For eksempel, hvis nogle af dine tekstdatoer er formateret som måned/dag/år (MDY), mens andre er dag/måned/år (DMY), og når du vælger 'DMY' i trin 3, vil du få forkerte resultater. Ligesom det er vist på billedet nedenfor.
Konvertering af kompleks tekststreng til dato
Tekst til kolonner-funktionen er praktisk, når du vil konvertere komplekse tekststrenge til datoer. Det giver dig mulighed for at bruge afgrænsningerne til at identificere, hvor dine data skal opdeles og vises på tværs af 2 eller flere kolonner. Og kombiner de opdelte dele af datoer til hel dato ved hjælp af DATE-funktionen.
For eksempel, hvis dine datoer vises i flerdelte tekststrenge, sådan her:
Onsdag den 1. februar 2020
1. februar 2020 kl. 16.10
Du kan bruge guiden Tekst til kolonne til at adskille oplysninger om dag, dato og klokkeslæt, der er afgrænset af et komma, og vise dem på tværs af flere kolonner.
Først skal du vælge alle tekststrenge, som du vil konvertere til datoer. Klik på knappen 'Tekst til kolonner' på fanen 'Data'. På trin 1 i guiden Tekst til kolonne skal du vælge indstillingen 'Afgrænset' under Original datatype og klikke på 'Næste'.
I trin 2 i guiden skal du vælge de afgrænsninger, som dine tekststrenge indeholder, og klikke på 'Næste'. Vores eksempeltekststrenge adskilt af komma og mellemrum – "mandag den 1. februar 2015 kl. 13:00". Vi bør vælge 'Komma' og 'mellemrum' som afgrænsninger for at opdele tekststrengene i flere kolonner.
I det sidste trin skal du vælge "Generelt"-formatet for alle kolonner i afsnittet Data Preview. Angiv, hvor kolonnerne skal indsættes i 'Destination'-feltet, hvis du ikke gør det, vil det overskrive de originale data. Hvis du vil ignorere en del af de originale data, skal du klikke på den i sektionen Data Preview og vælge muligheden 'Importér ikke kolonne (spring over)'. Klik derefter på 'Udfør'.
Nu er dele af datoer (ugedage, måned, år, klokkeslæt) opdelt i kolonne B, C, D, E, F og G.
Kombiner derefter datodelene sammen ved hjælp af en DATO-formel for at få hele datoen.
Syntaksen for Excel DATE-funktionen:
=DATO(år,måned,dag)I vores eksempel er måned-, dag- og årdelene i henholdsvis kolonne C, D og E.
DATO-funktionen genkender kun tal og ikke tekst. Da vores månedsværdier i kolonne C alle er tekststrenge, skal vi konvertere dem til tal. For at gøre det skal du bruge MÅNED-funktionen til at ændre navnet på måneden til månedens nummer.
For at konvertere en måneds navn til en måneds tal, skal du bruge denne MÅNED-funktion i DATO-funktionen:
=MÅNED(1&C1)MÅNED-funktionen tilføjer 1 til celle C2, som indeholder månedens navn for at konvertere månedsnavnet til det tilsvarende månedsnummer.
Dette er DATE-funktionen, vi skal bruge til at kombinere datodele fra forskellige kolonner:
=DATO(E1;MÅNED(1&C1);D1)
Brug nu udfyldningshåndtaget i det nederste hjørne af formelcellen og anvend formlen på kolonnen.
Konverter tekst til dato ved hjælp af metoden Find og erstat
Denne metode bruger afgrænsningstegn til at ændre tekstformatet til datoer. Hvis dag, måned og år i dine datoer er adskilt af en anden afgrænsning end en bindestreg (-) eller skråstreg (/), genkender Excel dem ikke som datoer og gemmer dem som tekst.
For at løse dette problem skal du bruge funktionen Find og erstat. Ved at ændre de ikke-standardiserede periodeafgrænsninger (.) med skråstreger (/) eller bindestreg (-), vil Excel automatisk ændre værdierne til datoer.
Først skal du vælge alle de tekstdatoer, du vil konvertere til datoer. På fanen 'Hjem' skal du klikke på knappen 'Find og vælg' i det højre hjørne af båndet og vælge 'Erstat'. Alternativt, tryk på Ctrl+H for at åbne dialogboksen Find og erstat.
I dialogboksen Find og erstat skal du skrive det skilletegn, som din tekst indeholder (i vores tilfælde punktum (.) i feltet 'Find hvad' og en skråstreg (/) eller bindestreg (-) i feltet 'Erstat med'. Klik på knappen 'Erstat alle' for at erstatte afgrænsningerne, og klik på 'Luk' for at lukke vinduet.
Nu genkender Excel, at dine tekststrenge nu er datoer, og det formaterer dem automatisk som datoer. Dine datoer vil blive justeret rigtigt som vist nedenfor.
Konverter tekst til dato ved hjælp af indsæt specialværktøj
En anden hurtig og nem måde at konvertere tekststrenge til datoer er ved at tilføje 0 til tekststrengen ved at bruge indsæt specialindstillingen. Tilføjelse af nul til værdi konverterer teksten til datoens serienummer, som du kan formatere som en dato.
Først skal du vælge en tom celle og kopiere den (vælg den og tryk på Ctrl + C at kopiere).
Vælg derefter de celler, der indeholder de tekstdatoer, du vil konvertere, højreklik og vælg indstillingen 'Indsæt speciel'.
I dialogboksen Indsæt speciel skal du vælge 'Alle' under Indsæt-sektionen, vælge 'Tilføj' under sektionen Betjening og klikke på 'OK'.
Du kan også udføre andre aritmetiske operationer subtrahere/multiplicere/divide værdien i destinationscellen med den indsatte værdi (som at gange celler med 1 eller dividere med 1 eller subtrahere nul).
Når du vælger 'Tilføj' i Operation, tilføjer det 'nul' til alle valgte tekstdatoer, da tilføjelse af et '0' ikke ændrer værdierne, får du serienummeret for hver dato. Nu skal du bare ændre formatet på cellerne.
Vælg serienumrene, og på fanen 'Hjem' skal du klikke på rullelisten 'Nummerformat' i Nummergruppen. Vælg muligheden 'Kort dato' fra rullemenuen.
Som du kan se nu, er tallene formateret som datoer og højrejusteret.
Konverter tekst til dato ved hjælp af formler
Der er to funktioner, der primært bruges til at konvertere tekst til dato: DATEVALUE og VALUE.
Brug af Excel DATEVALUE-funktionen
Excel DATEVALUE-funktionen er en af de nemmeste måder at konvertere en dato repræsenteret som tekst til en datos serienummer.
Syntaksen for DATEVALUE-funktionen:
=DATOVÆRDI(dato_tekst)Argument: dato_tekst angiver den tekststreng, du vil skjule, eller refererer til cellen, der indeholder tekstdatoer.
Formlen:
=DATOVÆRDI(A1)Følgende billede illustrerer, hvordan DATEVALUE-funktionen håndterer et par forskellige datoformater, som er gemt som tekst.
Du har datoens serienumre, nu skal du anvende datoformatet på disse numre. For at gøre det skal du vælge cellerne med serienumre og derefter gå til fanen 'Hjem' og vælge 'Kort dato' fra rullelisten 'Talformat'.
Nu har du dine formaterede datoer i kolonne C.
Selvom der ikke er nogen årstal i din tekstdato (A8), vil DATEVALUE bruge det aktuelle år fra din computers ur.
DATEVALUE-funktionen konverterer kun de tekstværdier, der ligner en dato. Det kan ikke konvertere en tekst, der ligner et tal til dato, og det kan heller ikke ændre en talværdi til dato, for det skal du bruge Excels VALUE-funktion.
Brug af Excel VALUE-funktionen
Excel VALUE-funktionen er i stand til at konvertere enhver tekststreng, der ligner en dato eller et tal, til en numerisk værdi, så det er virkelig nyttigt, når det kommer til at konvertere et hvilket som helst tal, ikke kun datoer.
Funktionen VÆRDI:
=VÆRDI(tekst)tekst– den tekststreng, vi ønsker at konvertere, eller refererer til cellen, der indeholder tekststrengen.
Eksempelformlen til at konvertere tekstdatoen:
=VÆRDI(A1)Nedenstående VALUE-formel kan ændre alle tekststrenge, der ligner en dato, til et tal som vist nedenfor.
VÆRDI-funktionen understøtter dog ikke alle typer datoværdier. For eksempel, hvis datoer bruger decimaler (A11), vil det returnere #VALUE! fejl.
Når du har serienummeret for din dato, skal du formatere cellen med datoens serienummer for at få det til at ligne en dato, som vi har gjort for funktionen DATOVÆRDI. For at gøre det skal du vælge serienumrene og vælge indstillingen 'Dato' fra rullemenuen 'Nummerformat' på fanen 'Hjem'.
Det er det, disse er de 5 forskellige måder, du kan konvertere datoer formateret som tekst til datoer i Excel.