Generisk formel
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Sammendrag
For å konvertere tekst i et ukjent datoformat til en riktig Excel-dato, kan du analysere teksten og sette sammen en riktig dato med en formel basert på flere funksjoner: DATO, VENSTRE, MIDT og HØYRE. I eksemplet vist er formelen i C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Denne formelen trekker ut år, måned og dag verdier hver for seg, og bruker DATE-funksjonen til å samle dem inn i datoen 24. oktober 2000.
Bakgrunn
Når du jobber med data fra et annet system, kan du komme i en situasjon der datoer ikke gjenkjennes riktig av Excel, som i stedet behandler datoene som tekst. For eksempel kan du ha tekstverdier som dette:
| Tekst | Dato representert |
| 20001024 | 24. oktober 2000 |
| 20050701 | 1. juli 2011 5 |
| 19980424 | 24. april 1998 |
| 28.02.2014 | 28. februar 2014 |
Når Excel har evaluert en datoverdi som tekst, er ett alternativ å bruke en formel for å analysere teksten i komponentene (år, måned, dag) og bruke disse til å lage en dato med DATO-funksjonen. Som nevnt ovenfor anbefaler jeg at du først prøver løsningene nedenfor (legger til null og bruker tekst til kolonner) før du bruker en formel. Begge løsningene er raskere og krever mindre innsats.
Forklaring
DATO-funksjonen oppretter en gyldig dato ved hjelp av tre argumenter: år, måned og dag:
=DATE(year,month,day)
I celle C6 bruker vi VENSTRE, MIDT og HØYRE funksjoner for å trekke ut hver av disse komponentene fra en tekststreng, og mate resultatene inn i DATO-funksjonen:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
VENSTRE-funksjonen trekker ut de fire venstre venstre for året, MID-funksjonen trekker ut tegn i posisjonene 5-6 for en måned, og den HØYRE-funksjonen trekker ut de to lengst til høyre som dagen. Hvert resultat returneres direkte til DATE-funksjonen. Det endelige resultatet er en skikkelig Excel-dato som kan formateres slik du vil.
Denne tilnærmingen kan tilpasses etter behov. For eksempel er det ukjente datoformatet i rad 8 dd.mm.åååå, og formelen i C8 er:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Langformet tekst
Noen ganger kan det hende du har datoer i lengre form som "11. apr 2020 08:43:13" som Excel ikke gjenkjenner riktig. I dette tilfellet kan du kanskje justere strengen på en måte som gjør det mulig for Excel å gjenkjenne datoen riktig med SUBSTITUTE-funksjonen. Formelen nedenfor erstatter den andre forekomsten av et mellomrom ("") med et komma og mellomrom (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Når vi har lagt til kommaet etter månedsnavnet, vil Excel forstå datoen, men det trenger fortsatt et lite "kick". Derfor legger vi til null på slutten. Matematikkoperasjonen får Excel til å prøve å konvertere strengen til et tall. Når det lykkes, vil dette resultere i en gyldig Excel-dato. Merk at det kan hende du må bruke datanummerformatering for å vise datoen riktig.
Uten formler
Før du bruker en formel for å analysere og konstruere en dato fra tekst manuelt, kan du prøve en av løsningene nedenfor. Det første alternativet bruker en matematikkoperasjon for å "knuse" Excel litt og tvinge den til å prøve og evaluere teksten som et tall. Fordi Excel-datoer faktisk er tall, kan dette ofte gjøre susen. Du må kanskje bruke et datoformat hvis operasjonene lykkes.
Legg til null for å fikse datoer
Noen ganger støter du på datoer i et tekstformat som Excel skal gjenkjenne. I dette tilfellet kan du kanskje tvinge Excel til å konvertere tekstverdiene til datoer ved å legge til null til verdien. Når du legger til null, vil Excel prøve å tvinge tekstverdier til tall. Siden datoer bare er tall, er dette trikset en fin måte å konvertere datoer i tekstformat som Excel virkelig burde forstå.
For å konvertere datoer på plass ved å legge til null, prøv Lim inn spesial:
- Angi null (0) i en ubrukt celle og kopier til utklippstavlen
- Velg de problematiske datoene
- Lim inn spesial> Verdier> Legg til
- Bruk et datoformat (om nødvendig)
Du kan også legge til null i en formel som denne:
=A1+0
der A1 inneholder en ukjent dato.
Tekst til kolonner for å fikse datoer
En annen måte å få Excel til å gjenkjenne datoer er å bruke Tekst til kolonner-funksjonen:
Velg datakolonnen, og prøv deretter Data> Tekst til kolonner> Fast> Fullfør
Hvis Excel gjenkjenner datoene, vil den fikse dem alle i ett trinn.
