Obstaja veliko načinov za pretvorbo datumov, oblikovanih kot besedilo, v dejanske datume v Excelu in cilj te vadnice je raziskati vse.
Ko uvozite podatke v Excel, so lahko v različnih oblikah, ki jih Excel ne prepozna. Včasih, ko so datumi uvoženi iz zunanjega vira, bodo prišli v obliki besedila.
Pomembno je tudi vedeti, da Excel uporablja formate datuma glede na nastavitve vaše sistemske regije. Ko torej v Excel uvozite podatke, ki so prišli iz druge države, jih Excel morda ne bo prepoznal in jih shranil kot vnose besedila.
Če imate to težavo, lahko uporabite veliko načinov za pretvorbo besedila v datum v Excelu in vse jih bomo obravnavali v tej vadnici.
Metode za pretvorbo besedila v datum
Če vaš delovni list vsebuje datume, oblikovane kot besedilo, namesto dejanskih datumov, jih ni mogoče uporabiti pri izračunih. Torej jih morate pretvoriti nazaj v dejanske datume.
Če vidite, da so vaši datumi poravnani na levo, to pomeni, da so oblikovani kot besedilo, ker so besedila privzeto poravnana levo. Številka in datumi so vedno poravnani v desno.
Obstaja nekaj različnih načinov, kako lahko besedilo pretvorite v datum v Excelu, in sicer:
- Možnost preverjanja napak
- Excelova funkcija besedila v stolpce
- Najdi in zamenjaj
- Posebno orodje za lepljenje
- Excelova formula in funkcije
Pretvorite besedilo v datum z uporabo možnosti preverjanja napak
Excel ima vgrajeno funkcijo preverjanja napak, ki odkrije nekaj očitnih napak v vaših podatkih. Če najde kakršno koli napako, vam bo v zgornjem levem kotu celice prikazal majhen zeleni trikotnik (indikator napake), ki vsebuje napako. Če izberete to celico, se prikaže opozorilni znak z rumenim klicajem. Ko premaknete kazalec na ta znak, vas bo Excel obvestil o možni težavi s to celico.
Na primer, ko v datum vnesete leto v dvomestni obliki, Excel ta datum prevzame kot besedilo in ga shrani kot besedilo. In če izberete to celico, se prikaže klicaj z opozorilom: »Ta celica vsebuje datumski niz, ki je predstavljen samo z dvema števkama leta«.
Če vaše celice prikazujejo ta indikator napake, kliknite klicaj in prikazalo se bo nekaj možnosti za pretvorbo datumov, oblikovanih kot besedilo, v dejanske datume. Excel vam bo pokazal možnosti za pretvorbo v 19XX ali 20XX (19XX za 1915, 20XX za 2015). Izberite ustrezno možnost.
Nato bo besedilo pretvorjeno v pravilno obliko datuma.
Kako omogočiti možnost preverjanja napak v Excelu
Običajno je možnost Preverjanje napak v Excelu privzeto vklopljena. Če vam funkcija preverjanja napak ne deluje, morate omogočiti preverjanje napak v Excelu.
Če želite to narediti, kliknite zavihek »Datoteka« in na levi plošči izberite »Možnosti«.
V oknu z možnostmi Excela kliknite »Formule« na levi plošči in na desni strani omogočite »Omogoči preverjanje napak v ozadju« v razdelku Preverjanje napak. In označite »Celice, ki vsebujejo leta, predstavljene kot 2 števki« v razdelku Pravila preverjanja napak.
Pretvorite besedilo v datum s funkcijo Excel Besedilo v stolpec
Besedilo v stolpec je odlična funkcija v Excelu, ki vam omogoča, da razdelite podatke v več stolpcev, in je tudi zmogljivo orodje za pretvorbo besedilnih vrednosti v vrednosti datuma. Ta metoda prepozna več različnih formatov podatkov in jih pretvori v ustrezno obliko datuma.
Pretvarjanje preprostih besedilnih nizov v datume
Recimo, da so vaši datumi oblikovani v besedilnih nizih, kot je ta:
S čarovnikom za besedilo v stolpce lahko hitro preoblikujete vse nazaj na datume.
Najprej izberite obseg besedilnih vnosov, ki jih želite pretvoriti v datume. Nato pojdite na zavihek »Podatki« na traku in kliknite možnost »Besedilo v stolpce« v skupini Podatkovna orodja.
Prikaže se čarovnik za besedilo v stolpce. V 1. koraku čarovnika za besedilo v stolpec izberite možnost »Delimited« pod Izvirna vrsta podatkov in kliknite »Naprej«.
V 2. koraku počistite vsa polja »Ločila« in kliknite Naprej.
V zadnjem koraku čarovnika izberite »Datum« pod Oblika podatkov stolpca in izberite obliko datuma na spustnem seznamu poleg »Datumi« in kliknite gumb »Dokončaj«. V našem primeru pretvarjamo besedilne datume, predstavljene kot »01 02 1995« (dan mesec leto), zato iz spustnega seznama »Datum:« izberemo »DMY«.
Zdaj Excel pretvori vaše besedilne datume v dejanske datume in jih v celicah prikaže desno poravnano.
Opomba: Preden začnete uporabljati funkcijo Besedilo v stolpec, se prepričajte, da so vsi vaši besedilni nizi v enaki obliki, sicer besedila ne bodo pretvorjena. Na primer, če so nekateri datumi vašega besedila oblikovani kot format mesec/dan/leto (MDY), drugi pa dan/mesec/leto (DMY), in ko v koraku 3 izberete »DMY«, bi dobili napačne rezultate. Kot je prikazano na spodnji sliki.
Pretvarjanje niza kompleksnega besedila v datum
Funkcija Besedilo v stolpce je priročna, če želite zapletene besedilne nize pretvoriti v datume. Omogoča vam, da uporabite ločila, da ugotovite, kje naj bodo vaši podatki razdeljeni in prikazani v 2 ali več stolpcih. In združite razdeljene dele datumov v cel datum s funkcijo DATE.
Na primer, če so vaši datumi prikazani v večdelnih besedilnih nizih, kot je ta:
Sreda, 01. februar 2020
1. februar 2020 ob 16.10
S čarovnikom Besedilo v stolpec lahko ločite informacije o dnevu, datumu in času, ki so razmejene z vejico, in jih prikažete v več stolpcih.
Najprej izberite vse besedilne nize, ki jih želite pretvoriti v datume. Kliknite gumb »Besedilo v stolpce« na zavihku »Podatki«. V 1. koraku čarovnika za besedilo v stolpec izberite možnost »Delimited« pod Izvirna vrsta podatkov in kliknite »Naprej«.
V 2. koraku čarovnika izberite ločila, ki jih vsebujejo vaši besedilni nizi, in kliknite »Naprej«. Naš primer besedilnih nizov, ločenih z vejico in presledkom – »ponedeljek, 1. februar 2015, 13:00«. Za ločila bi morali izbrati 'Vejica' in 'presledke', da razdelimo besedilne nize v več stolpcev.
V zadnjem koraku izberite obliko »Splošno« za vse stolpce v razdelku Predogled podatkov. Določite, kam naj bodo stolpci vstavljeni v polje »Cilj«, če tega ne storite, bodo izvirni podatki prepisani. Če želite prezreti del izvirnih podatkov, kliknite nanj v razdelku Predogled podatkov in izberite možnost »Ne uvozi stolpca (preskoči)«. Nato kliknite »Dokončaj«.
Sedaj so deli datumov (dnevi v tednu, mesec, leto, čas) razdeljeni v stolpce B, C, D, E, F in G.
Nato združite dele datuma skupaj s pomočjo formule DATE, da dobite celoten datum.
Sintaksa funkcije DATE v Excelu:
=DATE(leto,mesec,dan)V našem primeru so deli meseca, dneva in leta v stolpcih C, D in E.
Funkcija DATE prepozna samo številke in ne besedila. Ker so vse naše mesečne vrednosti v stolpcu C besedilni nizi, jih moramo pretvoriti v številke. Če želite to narediti, morate s funkcijo MESEC spremeniti ime meseca v številko meseca.
Če želite pretvoriti ime meseca v številko meseca, uporabite to funkcijo MESEC znotraj funkcije DATE:
=MESEC(1&C1)Funkcija MESEC doda 1 v celico C2, ki vsebuje ime meseca, da pretvori ime meseca v ustrezno številko meseca.
To je funkcija DATE, ki jo moramo uporabiti za združevanje delov datuma iz različnih stolpcev:
=DATUM(E1,MESEC(1&C1),D1)
Zdaj uporabite ročico za polnjenje v spodnjem kotu celice formule in uporabite formulo v stolpcu.
Pretvorite besedilo v datum z uporabo metode Najdi in zamenjaj
Ta metoda uporablja ločila za spreminjanje oblike besedila v datume. Če so dan, mesec in leto v vaših datumih ločeni z ločilom, ki ni pomišljaj (-) ali poševnica (/), jih Excel ne bo prepoznal kot datume in jih bo shranil kot besedilo.
Če želite odpraviti to težavo, uporabite funkcijo Najdi in zamenjaj. Če spremenite nestandardna ločila za obdobje (.) s poševnico (/) ali pomišljajem (-), bo Excel samodejno spremenil vrednosti v datume.
Najprej izberite vse besedilne datume, ki jih želite pretvoriti v datume. Na zavihku »Domov« kliknite gumb »Najdi in izberi« v skrajnem desnem kotu traku in izberite »Zamenjaj«. Druga možnost je, da pritisnete Ctrl+H da odprete pogovorno okno Najdi in zamenjaj.
V pogovornem oknu Najdi in zamenjaj vnesite ločilo, ki ga vsebuje vaše besedilo (v našem primeru piko (.) v polje 'Najdi', in poševnico naprej (/) ali pomišljaj (-)) v polje 'Zamenjaj z' . Kliknite gumb »Zamenjaj vse«, da zamenjate ločila, in kliknite »Zapri«, da zaprete okno.
Zdaj Excel prepozna, da so vaši besedilni nizi zdaj datumi, in jih samodejno oblikuje kot datume. Vaši datumi bodo poravnani, kot je prikazano spodaj.
Pretvorite besedilo v datum s posebnim orodjem za lepljenje
Drug hiter in enostaven način za pretvorbo besedilnih nizov v datume je dodajanje 0 besedilnemu nizu s posebno možnostjo lepljenja. Dodajanje nič vrednosti pretvori besedilo v serijsko številko datuma, ki jo lahko oblikujete kot datum.
Najprej izberite prazno celico in jo kopirajte (izberite jo in pritisnite Ctrl + C kopirati).
Nato izberite celice, ki vsebujejo besedilne datume, ki jih želite pretvoriti, z desno tipko miške kliknite in izberite možnost »Posebno lepljenje«.
V pogovornem oknu Posebno lepljenje izberite »Vse« v razdelku Prilepi, izberite »Dodaj« v razdelku Operacija in kliknite »V redu«.
Izvajate lahko tudi druge aritmetične operacije odštevanja/množitve/deljenja vrednosti v ciljni celici z prilepljeno vrednostjo (na primer pomnožite celice z 1 ali delite z 1 ali odštejete nič).
Ko izberete 'Dodaj' v operaciji, doda 'nič' vsem izbranim besedilnim datumom, ker dodajanje '0' ne spremeni vrednosti, boste dobili serijsko številko za vsak datum. Zdaj morate samo spremeniti obliko celic.
Izberite serijske številke in na zavihku »Domov« kliknite spustni seznam »Oblika številk« v skupini Številka. V spustnem meniju izberite možnost »Kratek datum«.
Kot lahko vidite, so številke oblikovane kot datumi in poravnane desno.
Pretvorite besedilo v datum z uporabo formul
Za pretvorbo besedila v datum se uporabljata predvsem dve funkciji: DATEVALUE in VALUE.
Uporaba funkcije Excel DATEVALUE
Excelova funkcija DATEVALUE je eden najpreprostejših načinov za pretvorbo datuma, predstavljenega kot besedilo, v serijsko številko datuma.
Sintaksa funkcije DATEVALUE:
=DATEVALUE(datum_text)Prepir: datum_besedilo določa besedilni niz, ki ga želite prikriti, ali se sklicuje na celico, ki vsebuje besedilne datume.
Formula:
=DATUMVREDNOST(A1)Naslednja slika ponazarja, kako funkcija DATEVALUE obravnava nekaj različnih formatov datuma, ki so shranjeni kot besedilo.
Dobili ste serijske številke datuma, zdaj morate za te številke uporabiti obliko datuma. Če želite to narediti, izberite celice s serijskimi številkami, nato pojdite na zavihek »Domov« in na spustnem seznamu »Oblika številk« izberite »Kratek datum«.
Zdaj imate oblikovane datume v stolpcu C.
Tudi če v vašem besedilnem datumu (A8) ni dela leta, bo DATEVALUE uporabil tekoče leto iz ure vašega računalnika.
Funkcija DATEVALUE bo pretvorila samo besedilne vrednosti, ki so videti kot datum. Besedila, ki je podobno številu, ne more pretvoriti na datum, niti ne more spremeniti številske vrednosti do datuma, za to boste potrebovali Excelovo funkcijo VALUE.
Uporaba funkcije Excel VALUE
Excelova funkcija VALUE lahko pretvori kateri koli besedilni niz, ki je podoben datumu ali številki, v številsko vrednost, zato je zelo koristna, ko gre za pretvorbo katere koli številke, ne samo datumov.
Funkcija VALUE:
=VREDNOST(besedilo)besedilo– besedilni niz, ki ga želimo pretvoriti, ali se sklicuje na celico, ki vsebuje besedilni niz.
Primer formule za pretvorbo besedilnega datuma:
=VREDNOST(A1)Spodnja formula VALUE lahko spremeni vse besedilne nize, ki so videti kot datum, v število, kot je prikazano spodaj.
Vendar funkcija VALUE ne podpira vseh vrst datumskih vrednosti. Na primer, če datumi uporabljajo decimalna mesta (A11), bo vrnil #VALUE! napaka.
Ko imate serijsko številko za svoj datum, boste morali oblikovati celico s serijsko številko datuma, da bo videti kot datum, kot smo to naredili za funkcijo DATEVALUE. Če želite to narediti, izberite serijske številke in izberite možnost »Datum« v spustnem meniju »Oblika številk« na zavihku »Domov«.
To je to, to je 5 različnih načinov, kako lahko datume, oblikovane kot besedilo, pretvorite v datume v Excelu.