Splošna formula
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Povzetek
Če želite pretvoriti besedilo v neprepoznani obliki zapisa datuma v ustrezen datum v Excelu, lahko besedilo razčlenite in sestavite ustrezen datum s formulo, ki temelji na več funkcijah: DATUM, LEVO, SREDNJA in DESNA. V prikazanem primeru je formula v C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ta formula ločeno izvleče vrednosti leta, meseca in dneva in jih s funkcijo DATE sestavi na datum 24. oktober 2000.
Ozadje
Ko delate s podatki iz drugega sistema, lahko pride do situacije, ko datumi Excel ne prepozna pravilno, temveč datume obravnava kot besedilo. Na primer, morda imate besedilne vrednosti, kot je ta:
| Besedilo | Datum zastopanja |
| 20001024 | 24. oktober 2000 |
| 20050701 | 1. julij 2011 |
| 19980424 | 24. aprila 1998 |
| 28.02.2014 | 28. februarja 2014 |
Ko je Excel vrednost datuma ocenil kot besedilo, je ena od možnosti uporaba formule za razčlenitev besedila na njegove komponente (leto, mesec, dan) in z njimi za določanje datuma s funkcijo DATUM. Kot je navedeno zgoraj, priporočam, da najprej preizkusite spodnje rešitve (dodajanje ničle in uporabo besedila v stolpce), preden uporabite formulo. Obe rešitvi sta hitrejši in zahtevata manj napora.
Pojasnilo
Funkcija DATE ustvari veljaven datum z uporabo treh argumentov: leto, mesec in dan:
=DATE(year,month,day)
V celici C6 s pomočjo funkcij LEFT, MID in RIGHT izločimo vsako od teh komponent iz besedilnega niza in rezultate posredujemo v funkcijo DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkcija LEVO izvleče skrajno leve 4 znake za leto, funkcija MID črpa znake na mestih 5-6 za mesec, funkcija DESNO pa skrajno dva desna znaka kot dan. Vsak rezultat se vrne neposredno v funkcijo DATE. Končni rezultat je ustrezen datum v Excelu, ki ga lahko oblikujete, kakor želite.
Ta pristop lahko prilagodite po potrebi. Na primer, neprepoznana oblika datuma v vrstici 8 je dd.mm.llll in formula v C8 je:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Besedilo v dolgi obliki
Včasih imate morda datume v daljši obliki, na primer »11. april 2020 08:43:13«, ki jih Excel ne prepozna pravilno. V tem primeru boste morda lahko niz prilagodili tako, da bo Excel s funkcijo SUBSTITUTE pravilno prepoznal datum. Spodnja formula nadomešča drugi primerek presledka ("") z vejico in presledkom (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Ko za imenom meseca dodamo vejico, bo Excel razumel datum, vendar je vseeno treba malo "brcniti". Zato na koncu dodamo ničlo. Zaradi matematične operacije Excel poskuša pretvoriti niz v število. Ko bo uspešen, bo to privedlo do veljavnega datuma v Excelu. Za pravilen prikaz datuma boste morda morali uporabiti oblikovanje številke datuma.
Brez formul
Preden uporabite formulo za ročno razčlenitev in oblikovanje datuma iz besedila, poskusite z enim od spodnjih popravkov. Prva možnost uporablja matematično operacijo, da Excel nekoliko "potisne" in prisili, da besedilo poskusi ovrednotiti kot številko. Ker so datumi v Excelu dejansko številke, je to pogosto lahko trik. Če je postopek uspešen, boste morda morali uporabiti obliko datuma.
Če želite določiti datume, dodajte nič
Včasih boste naleteli na datume v besedilni obliki, ki bi jo Excel moral prepoznati. V tem primeru boste morda lahko Excel prisilili, da pretvori besedilne vrednosti v datume, tako da vrednosti dodate nič. Ko dodate nič, bo Excel poskušal prisiliti besedilne vrednosti k številkam. Ker so datumi le številke, je ta trik odličen način za pretvorbo datumov v besedilno obliko, ki bi jo Excel zares razumel.
Če želite datume pretvoriti na mesto tako, da dodate ničlo, poskusite prilepiti:
- V neuporabljeno celico vnesite nič (0) in kopirajte v odložišče
- Izberite problematične datume
- Posebno lepljenje> Vrednosti> Dodaj
- Uporabi obliko datuma (če je potrebno)
V takšno formulo lahko dodate tudi nič:
=A1+0
kjer A1 vsebuje neprepoznan datum.
Besedilo v stolpce za določitev datumov
Drug način, kako lahko Excel prepozna datume, je uporaba funkcije Besedilo v stolpce:
Izberite stolpec z datumi, nato poskusite Podatki> Besedilo v stolpce> Popravljeno> Dokončaj
Če Excel prepozna datume, jih bo vse popravil v enem koraku.
