Postoji mnogo načina za pretvaranje datuma formatiranih kao tekst u stvarne datume u Excelu i ovaj vodič ima za cilj sve ih istražiti.
Kada uvozite podatke u Excel, oni mogu doći u mnogo različitih oblika koje Excel ne prepoznaje. Ponekad kada se datumi uvoze iz vanjskog izvora, oni će doći u formatu kao tekst.
Također je važno znati da Excel primjenjuje formate datuma na temelju postavki regije vašeg sustava. Dakle, kada u Excel uvezete podatke koji su došli iz druge zemlje, Excel ih možda neće prepoznati i pohraniti kao tekstualne unose.
Ako imate ovaj problem, postoji mnogo načina na koje možete pretvoriti tekst u datum u Excelu i sve ćemo ih pokriti u ovom vodiču.
Metode za pretvaranje teksta u datum
Ako vaš radni list sadrži datume formatirane kao tekst, umjesto stvarnih datuma, nije ih moguće koristiti u izračunima. Dakle, trebate ih pretvoriti natrag u stvarne datume.
Ako vidite da su vaši datumi poravnati ulijevo, to znači da su formatirani kao tekst jer su tekstovi prema zadanim postavkama poravnati ulijevo. A broj i datumi su uvijek poravnati udesno.
Postoji nekoliko različitih načina na koje možete pretvoriti tekst u datum u Excelu, a to su:
- Opcija provjere pogreške
- Značajka Excel teksta u stupce
- Pronađite i zamijenite
- Specijalni alat za lijepljenje
- Excel formula i funkcije
Pretvori tekst u datum pomoću opcije provjere grešaka
Excel ima ugrađenu značajku provjere grešaka koja uočava neke očite pogreške u vašim podacima. Ako pronađe bilo kakvu pogrešku, prikazat će vam mali zeleni trokut (indikator pogreške) u gornjem lijevom kutu ćelije, koji ima grešku. Ako odaberete tu ćeliju, pojavit će se znak za oprez sa žutim uskličnikom. Kada pomaknete pokazivač preko tog znaka, Excel će vas obavijestiti o mogućem problemu s tom ćelijom.
Na primjer, kada u datum unesete godinu u dvoznamenkastom formatu, Excel taj datum pretpostavlja kao tekst i pohranjuje ga kao tekst. A ako odaberete tu ćeliju, pojavit će se uskličnik s upozorenjem: 'Ova ćelija sadrži niz datuma predstavljen sa samo 2 znamenke godine'.
Ako vaše ćelije pokazuju taj indikator pogreške, kliknite na uskličnik i prikazat će se nekoliko opcija za pretvaranje datuma formatiranih kao tekst u stvarne datume. Excel će vam pokazati opcije da ga pretvorite u 19XX ili 20XX (19XX za 1915., 20XX za 2015.). Odaberite odgovarajuću opciju.
Zatim će se tekst pretvoriti u odgovarajući format datuma.
Kako omogućiti opciju provjere pogreške u Excelu
Obično je opcija Provjera pogreške uključena u Excelu prema zadanim postavkama. Ako vam značajka provjere pogreške ne radi, morate omogućiti provjeru pogrešaka u Excelu.
Da biste to učinili, kliknite karticu 'Datoteka' i odaberite 'Opcije' na lijevoj ploči.
U prozoru s opcijama programa Excel kliknite "Formule" na lijevoj ploči, a na desnoj strani omogućite "Omogući provjeru pogrešaka u pozadini" u odjeljku Provjera pogrešaka. I označite "Ćelije koje sadrže godine predstavljene kao 2 znamenke" u odjeljku Pravila za provjeru pogrešaka.
Pretvorite tekst u datum pomoću značajke Excel Text to Column
Tekst u stupac izvrsna je značajka u Excelu koja vam omogućuje da podijelite podatke u više stupaca, a također je moćan alat za pretvaranje tekstualnih vrijednosti u vrijednosti datuma. Ova metoda prepoznaje nekoliko različitih formata podataka i pretvara ih u odgovarajući format datuma.
Pretvaranje jednostavnih tekstualnih nizova u datume
Recimo da su vaši datumi oblikovani u tekstualne nizove ovako:
Možete koristiti čarobnjak za tekst u stupce da ih sve brzo preformatirate natrag na datume.
Najprije odaberite raspon tekstualnih unosa koje želite pretvoriti u datume. Zatim idite na karticu "Podaci" na vrpci i kliknite opciju "Tekst u stupce" u grupi Alati za podatke.
Pojavit će se čarobnjak za tekst u stupce. U 1. koraku čarobnjaka za tekst u stupac odaberite opciju "Razgraničeno" pod Izvorna vrsta podataka i kliknite "Dalje".
U koraku 2 poništite sve okvire "Razgraniči" i kliknite Dalje.
U posljednjem koraku čarobnjaka odaberite "Datum" pod Format podataka stupca i odaberite svoj format datuma s padajućeg popisa pored "Datuma" i kliknite gumb "Završi". U našem slučaju pretvaramo tekstualne datume predstavljene kao "01 02 1995" (dan mjesec godina), tako da biramo "DMY" s padajućeg popisa "Datum:".
Sada Excel pretvara vaše tekstualne datume u stvarne datume i prikazuje ih desno poravnate u ćelijama.
Bilješka: Prije nego što počnete koristiti značajku Text to Column, provjerite jesu li svi vaši tekstualni nizovi u identičnom formatu, inače se tekstovi neće pretvoriti. Na primjer, ako su neki od vaših tekstualnih datuma formatirani kao mjesec/dan/godina (MDY), dok su drugi dan/mjesec/godina (DMY), a kada odaberete 'DMY' u koraku 3, dobit ćete netočne rezultate. Kao što je prikazano na donjoj slici.
Pretvaranje složenog tekstualnog niza u datum
Značajka Text to Columns je zgodna kada želite pretvoriti složene tekstualne nizove u datume. Omogućuje vam korištenje graničnika da biste identificirali gdje bi vaši podaci trebali biti podijeljeni i prikazani u 2 ili više stupaca. I kombinirajte podijeljene dijelove datuma u cijeli datum pomoću funkcije DATE.
Na primjer, ako su vaši datumi prikazani u višedijelnim tekstualnim nizovima, poput ovoga:
Srijeda, 01. veljače 2020
1. veljače 2020. u 16.10 sati
Možete koristiti čarobnjak za tekst u stupac da biste odvojili podatke o danu, datumu i vremenu koji su razgraničeni zarezom i prikazali ih u više stupaca.
Najprije odaberite sve tekstualne nizove koje želite pretvoriti u datume. Kliknite gumb "Tekst u stupce" na kartici "Podaci". U 1. koraku čarobnjaka za tekst u stupac odaberite opciju "Razgraničeno" pod Izvorna vrsta podataka i kliknite "Dalje".
U 2. koraku čarobnjaka odaberite graničnike koje sadrže vaši tekstualni nizovi i kliknite "Dalje". Naš primjer tekstualnih nizova odvojenih zarezom i razmakom - "Ponedjeljak, 1. veljače 2015., 13:00". Trebali bismo odabrati 'Zarez' i 'razmake' kao graničnike da podijelimo tekstualne nizove u više stupaca.
U posljednjem koraku odaberite format "Općenito" za sve stupce u odjeljku Pregled podataka. Navedite gdje bi stupci trebali biti umetnuti u polje "Odredište", ako to ne učinite, prebrisat će se izvorni podaci. Ako želite zanemariti neki dio izvornih podataka, kliknite na njega u odjeljku Pregled podataka i odaberite opciju ‘Ne uvozi stupac (preskoči)’. Zatim kliknite "Završi".
Sada su dijelovi datuma (dani u tjednu, mjesec, godina, vrijeme) podijeljeni u stupce B, C, D, E, F i G.
Zatim spojite dijelove datuma uz pomoć formule DATE kako biste dobili cijeli datum.
Sintaksa funkcije Excel DATE:
=DATE(godina,mjesec,dan)U našem primjeru, dijelovi mjeseca, dana i godine nalaze se u stupcima C, D i E.
Funkcija DATE prepoznaje samo brojeve, a ne tekst. Budući da su naše mjesečne vrijednosti u stupcu C sve tekstualni nizovi, moramo ih pretvoriti u brojeve. Da biste to učinili, trebate upotrijebiti funkciju MJESEC za promjenu naziva mjeseca u broj mjeseca.
Da biste naziv mjeseca pretvorili u broj mjeseca, koristite ovu funkciju MJESEC unutar funkcije DATE:
=MJESEC(1&C1)Funkcija MJESEC dodaje 1 ćeliji C2 koja sadrži naziv mjeseca za pretvaranje naziva mjeseca u odgovarajući broj mjeseca.
Ovo je funkcija DATE koju moramo koristiti za kombiniranje dijelova datuma iz različitih stupaca:
=DATUM(E1,MJESEC(1&C1),D1)
Sada upotrijebite ručicu za popunjavanje u donjem kutu ćelije formule i primijenite formulu na stupac.
Pretvorite tekst u datum pomoću metode Find and Replace
Ova metoda koristi graničnike za promjenu formata teksta u datume. Ako su dan, mjesec i godina u vašim datumima odvojeni nekim graničnikom koji nije crtica (-) ili kosa crta (/), Excel ih neće prepoznati kao datume i nastavit će ih pohraniti kao tekst.
Da biste riješili ovaj problem, upotrijebite značajku Find and Replace. Promjenom nestandardnih graničnika razdoblja (.) s kosim crtama (/) ili crticom (-), Excel će automatski promijeniti vrijednosti u datume.
Najprije odaberite sve tekstualne datume koje želite pretvoriti u datume. Na kartici "Početna" kliknite gumb "Pronađi i odaberi" u krajnjem desnom kutu vrpce i odaberite "Zamijeni". Alternativno, pritisnite Ctrl+H da biste otvorili dijaloški okvir Pronađi i zamijeni.
U dijaloški okvir Pronađi i zamijeni upišite graničnik koji vaš tekst sadrži (u našem slučaju točku (.) u polje 'Pronađi što' i kosu crtu (/) ili crticu (-)) u polje 'Zamijeni s' . Kliknite gumb "Zamijeni sve" da biste zamijenili graničnike i kliknite "Zatvori" da biste zatvorili prozor.
Sada Excel prepoznaje da su vaši tekstualni nizovi sada datumi i automatski ih formatira kao datume. Vaši datumi će biti usklađeni kako je prikazano u nastavku.
Pretvorite tekst u datum pomoću posebnog alata za lijepljenje
Još jedan brz i jednostavan način pretvaranja tekstualnih nizova u datume je dodavanje 0 tekstualnom nizu pomoću posebne opcije zalijepi. Dodavanje nule vrijednosti pretvara tekst u serijski broj datuma koji možete oblikovati kao datum.
Prvo odaberite praznu ćeliju i kopirajte je (odaberite je i pritisnite Ctrl + C kopirati).
Zatim odaberite ćelije koje sadrže tekstualne datume koje želite pretvoriti, kliknite desnom tipkom miša i odaberite opciju "Specijalno zalijepi".
U dijaloškom okviru Posebno lijepljenje odaberite 'Sve' u odjeljku Zalijepi, odaberite 'Dodaj' u odjeljku Operacija i kliknite 'U redu'.
Također možete izvoditi druge aritmetičke operacije oduzimati/množiti/dijeliti vrijednost u odredišnoj ćeliji s zalijepljenom vrijednošću (kao što je množenje ćelija s 1 ili dijeljenje s 1 ili oduzimanje nule).
Kada odaberete "Dodaj" u operaciji, dodaje se "nula" svim odabranim tekstualnim datumima, budući da dodavanje "0" ne mijenja vrijednosti, dobit ćete serijski broj za svaki datum. Sada sve što trebate učiniti je promijeniti format ćelija.
Odaberite serijske brojeve i na kartici "Početna" kliknite na padajući popis "Format broja" u grupi Broj. Odaberite opciju "Kratak datum" s padajućeg izbornika.
Kao što sada možete vidjeti, brojevi su formatirani kao datumi i poravnati udesno.
Pretvorite tekst u datum pomoću formula
Dvije su funkcije koje se primarno koriste za pretvaranje teksta u datum: DATEVALUE i VALUE.
Korištenje funkcije Excel DATEVALUE
Excel funkcija DATEVALUE jedan je od najjednostavnijih načina za pretvaranje datuma predstavljenog kao tekst u serijski broj datuma.
Sintaksa funkcije DATEVALUE:
=DATEVALUE(datum_text)Argument: datum_tekst specificira tekstualni niz koji želite prikriti ili referencirati na ćeliju koja sadrži tekstualne datume.
formula:
=DATUMVRIJEDNOST(A1)Sljedeća slika ilustrira kako funkcija DATEVALUE obrađuje nekoliko različitih formata datuma koji su pohranjeni kao tekst.
Dobili ste serijske brojeve datuma, sada morate primijeniti format datuma na te brojeve. Da biste to učinili, odaberite ćelije sa serijskim brojevima, zatim idite na karticu "Početna" i odaberite "Kratak datum" s padajućeg popisa "Format broja".
Sada imate formatirane datume u stupcu C.
Čak i ako u tekstualnom datumu (A8) nema dijela godine, DATEVALUE će koristiti tekuću godinu iz sata vašeg računala.
Funkcija DATEVALUE će pretvoriti samo tekstualne vrijednosti koje izgledaju kao datum. Ne može pretvoriti tekst koji nalikuje broju na datum, niti može promijeniti brojčanu vrijednost do datuma, za to će vam trebati Excelova funkcija VRIJEDNOST.
Korištenje funkcije Excel VALUE
Excel funkcija VRIJEDNOST može pretvoriti bilo koji tekstualni niz koji nalikuje datumu ili broju u numeričku vrijednost, tako da je od velike pomoći kada je u pitanju pretvaranje bilo kojeg broja, a ne samo datuma.
Funkcija VALUE:
=VRIJEDNOST(tekst)tekst– tekstualni niz koji želimo pretvoriti ili referenca na ćeliju koja sadrži tekstualni niz.
Primjer formule za pretvaranje tekstualnog datuma:
=VRIJEDNOST(A1)Formula VALUE u nastavku može promijeniti sve tekstualne nizove koji izgledaju kao datum u broj kao što je prikazano u nastavku.
Međutim, funkcija VALUE ne podržava sve vrste vrijednosti datuma. Na primjer, ako datumi koriste decimalna mjesta (A11), vratit će se #VRIJEDNOST! pogreška.
Nakon što dobijete serijski broj za svoj datum, morat ćete formatirati ćeliju sa serijskim brojem datuma kako bi izgledala kao datum kao što smo to učinili za funkciju DATEVALUE. Da biste to učinili, odaberite serijske brojeve i odaberite opciju "Datum" s padajućeg izbornika "Format broja" na kartici "Početna".
To je to, ovo je 5 različitih načina na koje možete pretvoriti datume formatirane kao tekst u datume u Excelu.