Generisk formel
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Resumé
For at konvertere tekst i et ukendt datoformat til en korrekt Excel-dato kan du analysere teksten og samle en korrekt dato med en formel baseret på flere funktioner: DATO, VENSTRE, MIDT og HØJRE. I det viste eksempel er formlen i C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Denne formel udtrækker år-, måned- og dagværdierne separat og bruger DATE-funktionen til at samle dem til datoen 24. oktober 2000.
Baggrund
Når du arbejder med data fra et andet system, kan du komme i en situation, hvor datoer ikke genkendes korrekt af Excel, som i stedet behandler datoerne som tekst. For eksempel kan du have tekstværdier som denne:
| Tekst | Dato repræsenteret |
| 20001024 | 24. oktober 2000 |
| 20050701 | 1. juli 2011 5 |
| 19980424 | 24. april 1998 |
| 28.02.2014 | 28. februar 2014 |
Når Excel har evalueret en datoværdi som tekst, er en mulighed at bruge en formel til at analysere teksten i dens komponenter (år, måned, dag) og bruge disse til at lave en dato med DATE-funktionen. Som nævnt ovenfor anbefaler jeg, at du først prøver nedenstående løsninger (tilføjer nul og bruger tekst til kolonner), før du bruger en formel. Begge løsninger er hurtigere og kræver mindre indsats.
Forklaring
DATO-funktionen opretter en gyldig dato ved hjælp af tre argumenter: år, måned og dag:
=DATE(year,month,day)
I celle C6 bruger vi VENSTRE, MIDT og HØJRE-funktionerne til at udtrække hver af disse komponenter fra en tekststreng og føde resultaterne til DATO-funktionen:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
VENSTRE-funktionen ekstraherer de 4 venstre venstre for året, MID-funktionen ekstraherer tegn i positionerne 5-6 i måneden, og den HØJRE-funktion udpakker de 2 længst til højre som dag. Hvert resultat returneres direkte til DATE-funktionen. Det endelige resultat er en ordentlig Excel-dato, der kan formateres, som du vil.
Denne tilgang kan tilpasses efter behov. For eksempel er det ukendte datoformat i række 8 dd.mm.åååå, og formlen i C8 er:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Lang form tekst
Nogle gange har du muligvis datoer i en længere form som "11. apr 2020 08:43:13", som Excel ikke genkender korrekt. I dette tilfælde kan du muligvis justere strengen på en måde, der gør det muligt for Excel at genkende datoen korrekt med SUBSTITUTE-funktionen. Formlen nedenfor erstatter den anden forekomst af et mellemrum ("") med et komma og mellemrum (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Når vi først har tilføjet kommaet efter månedsnavnet, vil Excel forstå datoen, men det har stadig brug for et lille "kick". Derfor tilføjer vi nul i slutningen. Matematiske handlinger får Excel til at prøve at konvertere strengen til et tal. Når det lykkes, resulterer dette i en gyldig Excel-dato. Bemærk, at du muligvis skal anvende datonummerformatering for at vise datoen korrekt.
Uden formler
Inden du bruger en formel til manuelt at analysere og konstruere en dato ud fra tekst, skal du prøve en af rettelserne nedenfor. Den første mulighed bruger en matematisk operation til at "skubbe" Excel lidt og tvinge det til at prøve og evaluere teksten som et tal. Fordi Excel-datoer faktisk er tal, kan dette ofte gøre tricket. Du skal muligvis anvende et datoformat, hvis operationerne lykkes.
Tilføj nul for at fastsætte datoer
Nogle gange støder du på datoer i et tekstformat, som Excel skal genkende. I dette tilfælde kan du muligvis tvinge Excel til at konvertere tekstværdierne til datoer ved at tilføje nul til værdien. Når du tilføjer nul, forsøger Excel at tvinge tekstværdier til tal. Da datoer kun er tal, er dette trick en fantastisk måde at konvertere datoer i tekstformat, som Excel virkelig burde forstå.
For at konvertere datoer på plads ved at tilføje nul, prøv Indsæt speciel:
- Indtast nul (0) i en ubrugt celle, og kopier til udklipsholderen
- Vælg de problematiske datoer
- Indsæt specielt> Værdier> Tilføj
- Anvend et datoformat (hvis nødvendigt)
Du kan også tilføje nul i en formel som denne:
=A1+0
hvor A1 indeholder en ukendt dato.
Tekst til kolonner for at rette datoer
En anden måde at få Excel til at genkende datoer er at bruge funktionen Tekst til kolonner:
Vælg datakolonnen, og prøv derefter Data> Tekst til kolonner> Fast> Udfør
Hvis Excel genkender datoerne, løser det dem alle i et trin.
