Generička formula
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Sažetak
Da biste pretvorili tekst u neprepoznatom formatu datuma u odgovarajući Excel datum, možete raščlaniti tekst i sastaviti odgovarajući datum pomoću formule koja se temelji na nekoliko funkcija: DATUM, LIJEVO, SREDINA i DESNO. U prikazanom primjeru formula u C6 je:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ova formula izdvaja vrijednosti godine, mjeseca i dana odvojeno i koristi funkciju DATE za njihovo sastavljanje u datum 24. listopada 2000.
Pozadina
Kada radite s podacima iz drugog sustava, mogli biste naići na situaciju da datume Excel ne prepoznaje pravilno, a datume tretira kao tekst. Na primjer, možda imate tekstualne vrijednosti poput ove:
| Tekst | Datum predstavljanja |
| 20001024 | 24. listopada 2000 |
| 20050701 | 1. srpnja 20115 |
| 19980424 | 24. travnja 1998 |
| 28.02.2014 | 28. veljače 2014 |
Kad je Excel vrijednost datuma procijenio kao tekst, jedna od mogućnosti je uporaba formule za raščlanjivanje teksta na njegove komponente (godina, mjesec, dan) i pomoću njih za izradu datuma pomoću funkcije DATUM. Kao što je gore spomenuto, preporučujem da prije upotrebe formule prvo isprobate donja rješenja (dodavanje nule i korištenje teksta u stupce). Oba su rješenja brža i zahtijevaju manje napora.
Obrazloženje
Funkcija DATE stvara valjani datum pomoću tri argumenta: godina, mjesec i dan:
=DATE(year,month,day)
U ćeliji C6 koristimo funkcije LIJEVO, SREDINA i DESNO za izdvajanje svake od ovih komponenti iz tekstualnog niza i unos rezultata u funkciju DATUM:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkcija LIJEVA izdvaja krajnja lijeva 4 znaka za godinu, MID funkcija izdvaja znakove na položajima 5-6 za mjesec, a funkcija DESNA dva desna znaka kao dan. Svaki rezultat vraća se izravno u funkciju DATE. Konačni rezultat je pravilan Excel datum koji se može formatirati na bilo koji način.
Ovaj se pristup može prilagoditi prema potrebi. Na primjer, neprepoznati format datuma u retku 8 je dd.mm.yyyy, a formula u C8 je:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Tekst u dugom obliku
Ponekad možete imati datume u dužem obliku poput "11. travnja 2020. 08:43:13" koje Excel ne prepoznaje ispravno. U ovom slučaju možda ćete moći prilagoditi niz na način koji omogućava Excelu da ispravno prepozna datum pomoću funkcije ZAMJENA. Formula u nastavku zamjenjuje drugi primjerak razmaka ("") zarezom i razmakom (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Jednom kada dodamo zarez nakon naziva mjeseca, Excel će razumjeti datum, ali svejedno treba malo "udarca". Zato na kraju dodajemo nulu. Matematička operacija uzrokuje da Excel pokuša pretvoriti niz u broj. Kada uspije, to će rezultirati važećim datumom u Excelu. Imajte na umu da ćete možda trebati primijeniti oblikovanje broja datuma da biste ispravno prikazali datum.
Bez formula
Prije nego što upotrijebite formulu za ručno raščlanjivanje i konstruiranje datuma iz teksta, pokušajte s jednim od dolje navedenih popravaka. Prva opcija koristi matematičku operaciju kako bi malo "gurnula" Excel i prisilila ga da pokuša tekst procijeniti kao broj. Budući da su datumi u Excelu zapravo brojevi, to često može učiniti trik. Ako operacija uspije, možda ćete trebati primijeniti format datuma.
Dodajte nulu da biste popravili datume
Ponekad ćete susresti datume u tekstualnom formatu koji bi Excel trebao prepoznati. U tom slučaju možda ćete moći prisiliti Excel da pretvori tekstualne vrijednosti u datume dodavanjem vrijednosti nuli. Kada dodate nulu, Excel će pokušati prisiliti tekstualne vrijednosti na brojeve. Budući da su datumi samo brojevi, ovaj je trik izvrstan način za pretvaranje datuma u tekstualni format koji bi Excel doista trebao razumjeti.
Da biste datum pretvorili na mjesto dodavanjem nule, pokušajte zalijepiti posebno:
- Unesite nulu (0) u neiskorištenu ćeliju i kopirajte u međuspremnik
- Odaberite problematične datume
- Posebno zalijepite> Vrijednosti> Dodaj
- Primijenite format datuma (ako je potrebno)
U takvu formulu možete dodati i nulu:
=A1+0
gdje A1 sadrži neprepoznati datum.
Tekst u stupce radi popravljanja datuma
Drugi način da Excel nateče prepoznavanje datuma je upotreba značajke Tekst u stupce:
Odaberite stupac s datumima, a zatim pokušajte Podaci> Tekst u stupce> Fiksno> Završi
Ako Excel prepozna datume, sve će ih popraviti u jednom koraku.
