Det er mange måter å konvertere datoer formatert som tekst til faktiske datoer i Excel, og denne opplæringen tar sikte på å utforske dem alle.
Når du importerer data til Excel, kan de komme i mange forskjellige former som Excel ikke gjenkjenner. Noen ganger når datoer importeres fra en ekstern kilde, kommer de i formatert som tekst.
Det er også viktig å vite at Excel bruker datoformater basert på systeminnstillingene dine. Så når du importerer data til Excel som kom fra et annet land, kan det hende at Excel ikke gjenkjenner dem og lagrer dem som tekstoppføringer.
Hvis du har dette problemet, er det mange måter du kan bruke til å konvertere tekst til en dato i Excel, og vi vil dekke dem alle i denne opplæringen.
Metoder for å konvertere tekst til dato
Hvis regnearket ditt inneholder datoer formatert som tekst, i stedet for faktiske datoer, er det ikke mulig å bruke dem i noen beregninger. Så du må konvertere dem tilbake til faktiske datoer.
Hvis du ser datoene dine venstrejustert, betyr det at de er formatert som tekst fordi tekster er venstrejustert som standard. Og tallet og datoene er alltid justert til høyre.
Det er få forskjellige måter du kan konvertere tekst til en dato i Excel, de er:
- Alternativ for feilkontroll
- Excel Tekst til kolonner-funksjon
- Finn og erstatt
- Lim inn spesialverktøy
- Excel-formel og funksjoner
Konverter tekst til dato ved å bruke feilkontrollalternativet
Excel har en innebygd feilkontrollfunksjon som oppdager noen åpenbare feil i dataene dine. Hvis den finner en feil, vil den vise deg en liten grønn trekant (en feilindikator) i øvre venstre hjørne av cellen, som har feilen. Hvis du velger den cellen, vil et advarselsskilt med et gult utropstegn dukke opp. Når du flytter markøren over det tegnet, og Excel vil informere deg om det mulige problemet med den cellen.
For eksempel, når du skriver inn året i det tosifrede formatet i datoen, antar Excel den datoen som en tekst og lagrer den som tekst. Og hvis du velger den cellen, vil et utropstegn vises med en advarsel: 'Denne cellen inneholder en datostreng representert med kun 2-sifre i året'.
Hvis cellene dine viser den feilindikatoren, klikker du på utropstegnet, og det vil vise noen få alternativer for å konvertere datoer formatert som tekst til faktiske datoer. Excel vil vise deg alternativer for å konvertere den til 19XX eller 20XX (19XX for 1915, 20XX for 2015). Velg riktig alternativ.
Deretter vil teksten bli konvertert til riktig datoformat.
Slik aktiverer du alternativet for feilkontroll i Excel
Vanligvis er alternativet Feilkontroll slått på i Excel som standard. Hvis feilkontrollfunksjonen ikke fungerer for deg, må du aktivere feilkontroll i Excel.
For å gjøre dette, klikk på "Fil"-fanen og velg "Alternativer" i venstre panel.
I vinduet for Excel-alternativer, klikk "Formler" i venstre panel, og i panelet på høyre side aktiverer du "Aktiver bakgrunnsfeilkontroll" under Feilkontroll-delen. Og merk av for "Celler som inneholder år representert som 2 sifre" i delen for feilkontroll av regler.
Konverter tekst til dato ved å bruke Excel-tekst til kolonnefunksjon
Tekst til kolonne er en flott funksjon i Excel som lar deg dele data i flere kolonner, og det er også et kraftig verktøy for å konvertere tekstverdier til datoverdier. Denne metoden gjenkjenner flere forskjellige dataformater og konverterer dem til riktig datoformat.
Konvertering av enkle tekststrenger til datoer
La oss si at datoene dine er formatert i tekststrenger som dette:
Du kan bruke tekst til kolonner-veiviseren for raskt å formatere dem alle tilbake til dato.
Velg først rekkevidden av tekstoppføringer du vil konvertere til datoer. Gå deretter til "Data"-fanen på båndet og klikk på "Tekst til kolonner"-alternativet i Dataverktøy-gruppen.
Tekst til kolonner-veiviseren vises. På trinn 1 i tekst til kolonneveiviseren velger du alternativet "Avgrenset" under Original datatype og klikker på "Neste".
I trinn 2 fjerner du merket for alle 'Avgrensningsfelt' og klikker på Neste.
I det siste trinnet i veiviseren velger du "Dato" under Kolonnedataformat og velger datoformat fra rullegardinlisten ved siden av "Datoer", og klikker på "Fullfør"-knappen. I vårt tilfelle konverterer vi tekstdatoene representert som "01 02 1995" (dag måned år), så vi velger "DMY" fra rullegardinlisten "Dato:".
Nå konverterer Excel tekstdatoene dine til faktiske datoer og viser dem høyrejustert i cellene.
Merk: Før du begynner å bruke Tekst til kolonne-funksjonen, sørg for at alle tekststrengene dine er i identisk format, ellers blir ikke tekster konvertert. For eksempel, hvis noen av tekstdatoene dine er formatert som måned/dag/år (MDY), mens andre er dag/måned/år (DMY), og når du velger "DMY" i trinn 3, vil du få feil resultater. Som det er vist på bildet under.
Konvertering av kompleks tekststreng til dato
Tekst til kolonner-funksjonen er nyttig når du vil konvertere komplekse tekststrenger til datoer. Den lar deg bruke skilletegnene for å identifisere hvor dataene dine skal deles og vises på tvers av 2 eller flere kolonner. Og kombiner de delte delene av datoer til hel dato ved å bruke DATE-funksjonen.
For eksempel, hvis datoene dine vises i flerdelte tekststrenger, slik:
onsdag 1. februar 2020
1. februar 2020, 16.10
Du kan bruke tekst til kolonneveiviseren til å skille dag-, dato- og klokkeslettinformasjon som er avgrenset med komma, og vise dem på tvers av flere kolonner.
Velg først alle tekststrengene du vil konvertere til datoer. Klikk på "Tekst til kolonner"-knappen på "Data"-fanen. På trinn 1 i tekst til kolonneveiviseren velger du alternativet "Avgrenset" under Original datatype og klikker på "Neste".
I trinn 2 av veiviseren velger du skilletegnene tekststrengene inneholder og klikker på "Neste". Våre eksempeltekststrenger atskilt med komma og mellomrom – "mandag 1. februar 2015, 13:00". Vi bør velge "Komma" og "mellomrom" som skilletegn for å dele tekststrengene i flere kolonner.
I det siste trinnet velger du "Generelt"-formatet for alle kolonnene i Dataforhåndsvisning-delen. Spesifiser hvor kolonnene skal settes inn i 'Destinasjon'-feltet, hvis du ikke gjør det, vil det overskrive de originale dataene. Hvis du vil ignorere en del av de originale dataene, klikker du på den i Data Preview-delen og velger alternativet 'Ikke importer kolonne (hopp over)'. Klikk deretter på "Fullfør".
Nå er delene av datoer (ukedager, måned, år, klokkeslett) delt i kolonnene B, C, D, E, F og G.
Deretter kombinerer du datodelene ved hjelp av en DATO-formel for å få hele datoen.
Syntaksen til Excel DATE-funksjonen:
=DATO(år,måned,dag)I vårt eksempel er måneds-, dag- og årsdelene i henholdsvis kolonnene C, D og E.
DATE-funksjonen gjenkjenner bare tall, ikke tekst. Siden månedsverdiene våre i kolonne C alle er tekststrenger, må vi konvertere dem til tall. For å gjøre det, må du bruke MÅNED-funksjonen for å endre navnet på måneden til månedens nummer.
For å konvertere en måneds navn til en måneds tall, bruk denne MÅNED-funksjonen i DATO-funksjonen:
=MÅNED(1&C1)MÅNED-funksjonen legger til 1 til celle C2 som inneholder navnet på måneden for å konvertere månedsnavnet til det tilsvarende månedsnummeret.
Dette er DATO-funksjonen vi må bruke for å kombinere datodeler fra forskjellige kolonner:
=DATO(E1;MÅNED(1&C1);D1)
Bruk nå fyllhåndtaket nederst i hjørnet av formelcellen og bruk formelen på kolonnen.
Konverter tekst til dato ved å bruke Finn og erstatt-metoden
Denne metoden bruker skilletegn for å endre formatet på tekst til datoer. Hvis dag, måned og år i datoene dine er atskilt med et annet skilletegn enn en bindestrek (-) eller skråstrek (/), vil ikke Excel gjenkjenne dem som datoer og lagre dem som tekst.
For å fikse dette problemet, bruk Finn og erstatt-funksjonen. Ved å endre ikke-standard periodeskilletegn (.) med skråstreker (/) eller bindestrek (-), vil Excel automatisk endre verdiene til datoer.
Velg først alle tekstdatoene du vil konvertere til datoer. På "Hjem"-fanen klikker du på "Finn og velg"-knappen i det høyre hjørnet av båndet og velger "Erstatt". Alternativt, trykk Ctrl+H for å åpne dialogboksen Finn og erstatt.
I dialogboksen Finn og erstatt skriver du inn skilletegnet som teksten inneholder (i vårt tilfelle punktum (.) i "Finn hva"-feltet og en skråstrek (/) eller bindestrek (-) i "Erstatt med"-feltet Klikk "Erstatt alle"-knappen for å erstatte skilletegnene og klikk "Lukk" for å lukke vinduet.
Nå gjenkjenner Excel at tekststrengene dine nå er datoer, og den formaterer dem automatisk som datoer. Datoene dine vil bli justert som vist nedenfor.
Konverter tekst til dato ved å bruke spesialverktøyet Lim inn
En annen rask og enkel måte å konvertere tekststrenger til datoer er ved å legge til 0 i tekststrengen ved å bruke spesialalternativet lim inn. Hvis du legger til null til verdien, konverteres teksten til datoens serienummer som du kan formatere som en dato.
Velg først en tom celle og kopier den (velg den og trykk Ctrl + C å kopiere).
Velg deretter cellene som inneholder tekstdatoene du vil konvertere, høyreklikk og velg alternativet "Lim inn spesial".
I Paste Special-dialogen, velg "Alle" under Paste-delen, velg "Add" under Operation-delen, og klikk "OK".
Du kan også utføre andre aritmetiske operasjoner subtrahere/multiplisere/dividere verdien i målcellen med den limte verdien (som å multiplisere celler med 1 eller dele på 1 eller subtrahere null).
Når du velger "Legg til" i operasjon, legger den til "null" til alle valgte tekstdatoer, siden det å legge til en "0" ikke endrer verdiene, får du serienummeret for hver dato. Nå er alt du trenger å gjøre å endre formatet på cellene.
Velg serienumrene og på "Hjem"-fanen klikker du på rullegardinlisten "Tallformat" i Nummergruppen. Velg alternativet "Kort dato" fra rullegardinmenyen.
Som du kan se nå er tallene formatert som datoer og rettjustert.
Konverter tekst til dato ved hjelp av formler
Det er to funksjoner som primært brukes til å konvertere tekst til dato: DATOVERDI og VERDI.
Bruker Excel DATEVALUE-funksjonen
Excel DATOVERDI-funksjonen er en av de enkleste måtene å konvertere en dato representert som tekst til en datos serienummer.
Syntaksen til DATEVALUE-funksjonen:
=DATOVERDI(dato_tekst)Argument: dato_tekst spesifiserer tekststrengen du vil skjule eller refererer til cellen som inneholder tekstdatoer.
Formelen:
=DATOVERDI(A1)Følgende bilde illustrerer hvordan DATOVERDI-funksjonen håndterer noen få forskjellige datoformater som lagres som tekst.
Du har datoens serienumre, nå må du bruke datoformatet på disse tallene. For å gjøre det, velg cellene med serienumre, gå deretter til fanen "Hjem" og velg "Kort dato" fra rullegardinlisten "Tallformat".
Nå har du formaterte datoer i kolonne C.
Selv om det ikke er noen årstall i tekstdatoen (A8), vil DATEVALUE bruke gjeldende år fra datamaskinens klokke.
DATOVERDI-funksjonen vil bare konvertere tekstverdiene som ser ut som en dato. Den kan ikke konvertere en tekst som ligner et tall til dato, og den kan heller ikke endre en tallverdi til dato, for det trenger du Excels VERDI-funksjon.
Bruke Excel VALUE-funksjonen
Excel VALUE-funksjonen er i stand til å konvertere en hvilken som helst tekststreng som ligner en dato eller et tall til en numerisk verdi, så det er veldig nyttig når det gjelder å konvertere et hvilket som helst tall, ikke bare datoer.
VERDI-funksjonen:
=VERDI(tekst)tekst– tekststrengen vi ønsker å konvertere eller refererer til cellen som inneholder tekststrengen.
Eksempelformelen for å konvertere tekstdatoen:
=VERDI(A1)Verdiformelen nedenfor kan endre tekststrenger som ser ut som en dato til et tall som vist nedenfor.
VALUE-funksjonen støtter imidlertid ikke alle typer datoverdier. Hvis for eksempel datoer bruker desimaler (A11), vil det returnere #VALUE! feil.
Når du har serienummeret for datoen din, må du formatere cellen med datoens serienummer for å få det til å se ut som en dato slik vi har gjort for DATOVERDI-funksjonen. For å gjøre det, velg serienumrene og velg alternativet "Dato" fra rullegardinmenyen "Tallformat" i fanen "Hjem".
Det er det, dette er de 5 forskjellige måtene du kan konvertere datoer formatert som tekst til datoer i Excel.