Generisk formel
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Sammanfattning
För att konvertera text i ett okänt datumformat till ett korrekt Excel-datum kan du analysera texten och sätta ihop ett korrekt datum med en formel baserad på flera funktioner: DATUM, VÄNSTER, MIDDAG och HÖGER. I exemplet som visas är formeln i C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Den här formeln extraherar värdena för år, månad och dag separat och använder DATE-funktionen för att sätta ihop dem till datumet 24 oktober 2000.
Bakgrund
När du arbetar med data från ett annat system kan du komma i en situation där datum inte känns igen korrekt av Excel, som istället behandlar datum som text. Du kan till exempel ha textvärden så här:
| Text | Datum representerat |
| 20001024 | 24 oktober 2000 |
| 20050701 | 1 juli 2011 5 |
| 19980424 | 24 april 1998 |
| 28.02.2014 | 28 februari 2014 |
När Excel har utvärderat ett datumvärde som text, är ett alternativ att använda en formel för att analysera texten i dess komponenter (år, månad, dag) och använda dessa för att skapa ett datum med funktionen DATUM. Som nämnts ovan rekommenderar jag att du först försöker lösningarna nedan (lägga till noll och använda text i kolumner) innan du använder en formel. Båda lösningarna är snabbare och kräver mindre ansträngning.
Förklaring
Funktionen DATE skapar ett giltigt datum med tre argument: år, månad och dag:
=DATE(year,month,day)
I cell C6 använder vi VÄNSTER-, MID- och HÖGER-funktionerna för att extrahera var och en av dessa komponenter från en textsträng och mata resultaten till DATE-funktionen:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
VÄNSTER-funktionen extraherar de fyra längst till vänster för året, MID-funktionen extraherar tecken i positionerna 5-6 under månaden, och HÖGER-funktionen extraherar de två längst till höger som dagen. Varje resultat returneras direkt till DATE-funktionen. Det slutliga resultatet är ett ordentligt Excel-datum som kan formateras som du vill.
Detta tillvägagångssätt kan anpassas efter behov. Till exempel är det okända datumformatet i rad 8 dd.mm.åååå och formeln i C8 är:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Långformig text
Ibland kan du ha datum i en längre form som "11 apr 2020 08:43:13" som Excel inte känner igen ordentligt. I det här fallet kanske du kan justera strängen på ett sätt som gör att Excel korrekt kan känna igen datumet med SUBSTITUTE-funktionen. Formeln nedan ersätter den andra instansen av ett mellanslag ("") med ett komma och mellanslag (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
När vi väl lagt till komma efter månadsnamnet kommer Excel att förstå datumet, men det behöver fortfarande en liten "kick". Det är därför vi lägger till noll i slutet. Matematiken gör att Excel försöker konvertera strängen till ett tal. När det lyckas resulterar detta i ett giltigt Excel-datum. Observera att du kan behöva använda datumnummerformatering för att visa datumet korrekt.
Utan formler
Innan du använder en formel för att manuellt analysera och skapa ett datum från text, försök med en av korrigeringarna nedan. Det första alternativet använder en matteoperation för att "knuffa" Excel lite och tvinga det att försöka utvärdera texten som ett tal. Eftersom Excel-datum faktiskt är siffror kan detta ofta göra tricket. Du kan behöva använda ett datumformat om operationerna lyckas.
Lägg till noll för att fixa datum
Ibland stöter du på datum i ett textformat som Excel ska känna igen. I det här fallet kan du kanske tvinga Excel att konvertera textvärdena till datum genom att lägga till noll till värdet. När du lägger till noll kommer Excel att försöka tvinga textvärden till siffror. Eftersom datum bara är siffror är det här tricket ett utmärkt sätt att konvertera datum i textformat som Excel verkligen borde förstå.
För att konvertera datum på plats genom att lägga till noll, prova Klistra in Special:
- Ange noll (0) i en oanvänd cell och kopiera till urklipp
- Välj de problematiska datumen
- Klistra in Special> Värden> Lägg till
- Tillämpa ett datumformat (om det behövs)
Du kan också lägga till noll i en sådan formel:
=A1+0
där A1 innehåller ett okänt datum.
Texta till kolumner för att fixa datum
Ett annat sätt att få Excel att känna igen datum är att använda funktionen Text till kolumner:
Välj datumkolumnen och försök sedan Data> Text till kolumner> Fixad> Slutför
Om Excel känner igen datumen fixar de alla i ett steg.
