Det finns många sätt att konvertera datum formaterade som text till faktiska datum i Excel och den här handledningen syftar till att utforska dem alla.
När du importerar data till Excel kan det komma i många olika former som Excel inte känner igen. Ibland när datum importeras från en extern källa kommer de i formaterad text.
Det är också viktigt att veta att Excel tillämpar datumformat baserat på dina systemregioninställningar. Så när du importerar data till Excel som kom från ett annat land kanske Excel inte känner igen dem och lagrar dem som textposter.
Om du har det här problemet finns det många sätt du kan använda för att konvertera text till ett datum i Excel och vi kommer att täcka dem alla i den här handledningen.
Metoder för att konvertera text till datum
Om ditt kalkylblad innehåller datum formaterade som text, istället för faktiska datum, är det inte möjligt att använda dem i några beräkningar. Så du måste konvertera dem tillbaka till faktiska datum.
Om du ser dina datum vänsterjusterade betyder det att de är formaterade som text eftersom texter är vänsterjusterade som standard. Och numret och datumen är alltid högerjusterade.
Det finns några olika sätt du kan konvertera text till ett datum i Excel, de är:
- Alternativ för felkontroll
- Excel-funktion för text till kolumner
- Hitta & ersätt
- Klistra in specialverktyg
- Excel formel och funktioner
Konvertera text till datum med alternativet för felkontroll
Excel har en inbyggd felkontrollfunktion som upptäcker några uppenbara fel i dina data. Om den hittar något fel kommer den att visa dig en liten grön triangel (en felindikator) i det övre vänstra hörnet av cellen, som har felet. Om du markerar den cellen kommer ett varningstecken med ett gult utropstecken att dyka upp. När du flyttar markören över det tecknet kommer Excel att informera dig om det möjliga problemet med den cellen.
Till exempel, när du anger årtalet i tvåsiffrigt format i ditt datum, antar Excel det datumet som en text och lagrar det som text. Och om du väljer den cellen kommer ett utropstecken att visas med en varning: 'Denna cell innehåller en datumsträng representerad med endast två siffror i året'.
Om dina celler visar den felindikatorn, klicka på utropstecknet, och det kommer att visa några alternativ för att konvertera datum formaterade som text till faktiska datum. Excel kommer att visa dig alternativ för att konvertera den till 19XX eller 20XX (19XX för 1915, 20XX för 2015). Välj lämpligt alternativ.
Sedan kommer texten att konverteras till korrekt datumformat.
Hur man aktiverar felkontrollalternativ i Excel
Vanligtvis är alternativet Felkontroll aktiverat i Excel som standard. Om felkontrollfunktionen inte fungerar för dig måste du aktivera felkontrollen i Excel.
För att göra detta, klicka på fliken "Arkiv" och välj "Alternativ" i den vänstra panelen.
I fönstret för Excel-alternativ klickar du på "Formler" i den vänstra panelen och i panelen till höger, aktivera "Aktivera bakgrundsfelkontroll" under avsnittet Felkontroll. Och markera "Celler som innehåller år representerade som 2 siffror" i avsnittet för regler för felkontroll.
Konvertera text till datum med hjälp av Excel-funktion för text till kolumn
Text till kolumn är en fantastisk funktion i Excel som låter dig dela upp data i flera kolumner, och det är också ett kraftfullt verktyg för att konvertera textvärden till datumvärden. Denna metod känner igen flera olika dataformat och konverterar dem till korrekt datumformat.
Konvertera enkla textsträngar till datum
Låt oss säga att dina datum är formaterade i textsträngar så här:
Du kan använda guiden Text till kolumner för att snabbt formatera om alla tillbaka till datum.
Välj först intervallet för textposter du vill konvertera till datum. Gå sedan till fliken "Data" i menyfliksområdet och klicka på alternativet "Text till kolumner" i gruppen Dataverktyg.
Guiden Text till kolumner visas. I steg 1 i guiden Text till kolumn väljer du alternativet "Avgränsat" under Originaldatatyp och klickar på "Nästa".
I steg 2, avmarkera alla "Avgränsare"-rutor och klicka på Nästa.
I det sista steget av guiden, välj "Datum" under Kolumndataformat och välj ditt datumformat från rullgardinsmenyn bredvid "Datum" och klicka på "Slutför"-knappen. I vårt fall konverterar vi textdatumen representerade som "01 02 1995" (dag månad år), så vi väljer "DMY" från rullgardinsmenyn "Datum:".
Nu konverterar Excel dina textdatum till faktiska datum och visar dem högerjusterade i cellerna.
Notera: Innan du börjar använda funktionen Text till kolumn, se till att alla dina textsträngar är i ett identiskt format, annars kommer texter inte att konverteras. Till exempel, om några av dina textdatum är formaterade som månad/dag/år (MDY)-format medan andra är dag/månad/år (DMY), och när du väljer "DMY" i steg 3, skulle du få felaktiga resultat. Som det visas på bilden nedan.
Konvertera komplex textsträng till datum
Funktionen Text till kolumner är praktisk när du vill konvertera komplexa textsträngar till datum. Det låter dig använda avgränsare för att identifiera var dina data ska delas och visas i 2 eller fler kolumner. Och kombinera de delade delarna av datum till ett helt datum med hjälp av DATUM-funktionen.
Till exempel, om dina datum visas i flerdelade textsträngar, så här:
Onsdagen den 1 februari 2020
1 februari 2020, 16.10
Du kan använda guiden Text till kolumn för att separera dag-, datum- och tidsinformation som är avgränsad med kommatecken och visa dem i flera kolumner.
Välj först alla textsträngar som du vill konvertera till datum. Klicka på knappen "Text till kolumner" på fliken "Data". I steg 1 i guiden Text till kolumn väljer du alternativet "Avgränsat" under Originaldatatyp och klickar på "Nästa".
I steg 2 i guiden väljer du de avgränsare som dina textsträngar innehåller och klickar på "Nästa". Våra exempeltextsträngar separerade med kommatecken och blanksteg – "måndag 1 februari 2015, 13:00". Vi bör välja "Komma" och "mellanslag" som avgränsare för att dela upp textsträngarna i flera kolumner.
I det sista steget väljer du formatet "Allmänt" för alla kolumner i avsnittet Förhandsgranskning av data. Ange var kolumnerna ska infogas i fältet "Destination", om du inte gör det kommer det att skriva över originaldata. Om du vill ignorera en del av originaldata, klicka på den i avsnittet Dataförhandsgranskning och välj alternativet "Importera inte kolumn (hoppa över)". Klicka sedan på "Slutför".
Nu är delarna av datum (veckodagar, månad, år, tid) uppdelade i kolumnerna B, C, D, E, F och G.
Kombinera sedan datumdelarna med hjälp av en DATUM-formel för att få hela datumet.
Syntaxen för Excel DATE-funktionen:
=DATE(år,månad,dag)I vårt exempel finns månads-, dag- och årsdelarna i kolumnerna C, D respektive E.
DATUM-funktionen känner bara igen siffror inte text. Eftersom våra månadsvärden i kolumn C alla är textsträngar måste vi konvertera dem till siffror. För att göra det måste du använda funktionen MÅNAD för att ändra namnet på månaden till månadens nummer.
För att konvertera en månads namn till en månads nummer, använd denna MÅNAD-funktion i DATUM-funktionen:
=MÅNAD(1&C1)Funktionen MÅNAD lägger till 1 i cell C2 som innehåller månadens namn för att konvertera månadsnamnet till motsvarande månadsnummer.
Det här är DATE-funktionen vi behöver använda för att kombinera datumdelar från olika kolumner:
=DATUM(E1,MÅNAD(1&C1),D1)
Använd nu fyllningshandtaget i det nedre hörnet av formelcellen och tillämpa formeln på kolumnen.
Konvertera text till datum med hjälp av metoden Sök och ersätt
Den här metoden använder avgränsare för att ändra formatet på text till datum. Om dag, månad och år i dina datum separeras med någon annan avgränsare än ett bindestreck (-) eller snedstreck (/), kommer Excel inte att känna igen dem som datum och kommer att lagra dem som text.
För att åtgärda det här problemet, använd funktionen Sök och ersätt. Genom att ändra de icke-standardiserade periodavgränsarna (.) med snedstreck (/) eller bindestreck (-), kommer Excel automatiskt att ändra värdena till datum.
Välj först alla textdatum som du vill konvertera till datum. På fliken "Hem", klicka på knappen "Sök och välj" längst till höger på menyfliksområdet och välj "Ersätt". Alternativt, tryck Ctrl+H för att öppna dialogrutan Sök och ersätt.
I dialogrutan Sök och ersätt skriver du avgränsaren som din text innehåller (i vårt fall punkt (.) i fältet "Hitta vad" och ett snedstreck (/) eller bindestreck (-) i fältet "Ersätt med" Klicka på knappen "Ersätt alla" för att ersätta avgränsarna och klicka på "Stäng" för att stänga fönstret.
Nu inser Excel att dina textsträngar nu är datum och det formaterar dem automatiskt som datum. Dina datum kommer att justeras rätt som visas nedan.
Konvertera text till datum med hjälp av specialverktyget Klistra in
Ett annat snabbt och enkelt sätt att konvertera textsträngar till datum är genom att lägga till 0 till textsträngen med hjälp av specialalternativet klistra in. Genom att lägga till noll till värdet konverteras texten till datumets serienummer som du kan formatera som ett datum.
Välj först en tom cell och kopiera den (välj den och tryck Ctrl + C att kopiera).
Välj sedan cellerna som innehåller textdatumen du vill konvertera, högerklicka och välj alternativet "Klistra in special".
I dialogrutan Klistra in special, välj "Alla" under Klistra in, välj "Lägg till" under operationssektionen och klicka på "OK".
Du kan också utföra andra aritmetiska operationer subtrahera/multiplicera/dividera värdet i målcellen med det inklistrade värdet (som multiplicera celler med 1 eller dividera med 1 eller subtrahera noll).
När du väljer "Lägg till" i Operation lägger den till "noll" till alla valda textdatum, eftersom att lägga till en "0" inte ändrar värdena, får du serienumret för varje datum. Nu är allt du behöver göra att ändra formatet på cellerna.
Välj serienumren och på fliken "Hem" klickar du på rullgardinsmenyn "Nummerformat" i Nummergruppen. Välj alternativet "Kort datum" i rullgardinsmenyn.
Som du kan se nu är siffrorna formaterade som datum och rättjusterade.
Konvertera text till datum med formler
Det finns två funktioner som främst används för att konvertera text till datum: DATUMVÄRDE och VÄRDE.
Använder Excel DATEVALUE-funktionen
Excel DATUMVÄRDE-funktionen är ett av de enklaste sätten att konvertera ett datum representerat som text till ett datums serienummer.
Syntaxen för DATEVALUE-funktionen:
=DATUMVÄRDE(datum_text)Argument: datum_text anger textsträngen du vill dold eller refererar till cellen som innehåller textdatum.
Formeln:
=DATUMVÄRDE(A1)Följande bild illustrerar hur DATUMVÄRDE-funktionen hanterar några olika datumformat som lagras som text.
Du har datumets serienummer, nu måste du använda datumformatet på dessa nummer. För att göra det, välj cellerna med serienummer, gå sedan till fliken "Hem" och välj "Kort datum" från rullgardinsmenyn "Nummerformat".
Nu har du dina formaterade datum i kolumn C.
Även om det inte finns någon årsdel i ditt textdatum (A8), kommer DATEVALUE att använda det aktuella året från din dators klocka.
Funktionen DATUMVÄRDE konverterar bara textvärdena som ser ut som ett datum. Det kan inte konvertera en text som liknar ett nummer till datum, och det kan inte heller ändra ett talvärde till datum, för det behöver du Excels VÄRDE-funktion.
Använder Excel VALUE-funktionen
Excel VALUE-funktionen kan konvertera vilken textsträng som helst som liknar ett datum eller nummer till ett numeriskt värde, så det är verkligen användbart när det gäller att konvertera valfritt tal, inte bara datum.
Funktionen VÄRDE:
=VÄRDE(text)text– textsträngen vi vill konvertera eller refererar till cellen som innehåller textsträngen.
Exempelformeln för att konvertera textdatumet:
=VÄRDE(A1)VÄRDE-formeln nedan kan ändra alla textsträngar som ser ut som ett datum till ett tal som visas nedan.
Funktionen VALUE stöder dock inte alla typer av datumvärden. Till exempel, om datum använder decimaler (A11), kommer det att returnera #VALUE! fel.
När du har serienumret för ditt datum måste du formatera cellen med datumets serienummer för att få det att se ut som ett datum som vi har gjort för funktionen DATUMVÄRDE. För att göra det, välj serienumren och välj alternativet "Datum" från rullgardinsmenyn "Nummerformat" på fliken "Hem".
Det är det, det här är de 5 olika sätten du kan konvertera datum formaterade som text till datum i Excel.