Vispārēja formula
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Kopsavilkums
Lai tekstu neatpazītā datuma formātā pārvērstu par pareizu Excel datumu, varat parsēt tekstu un salikt pareizu datumu ar formulu, kas balstīta uz vairākām funkcijām: DATUMS, LEFT, LID, MID un RIGHT. Parādītajā piemērā formula C6 ir:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Šī formula atsevišķi iegūst gada, mēneša un dienas vērtības un izmanto funkciju DATE, lai tās apkopotu datumā, kas datēts ar 2000. gada 24. oktobri.
Priekšvēsture
Strādājot ar datiem no citas sistēmas, iespējams, nonākat situācijā, kad datumi netiek pareizi atpazīti programmā Excel, un tā vietā datumus apstrādā kā tekstu. Piemēram, jums var būt šādas teksta vērtības:
| Teksts | Pārstāvēšanas datums |
| 20001024 | 2000. gada 24. oktobris |
| 20050701 | 2011. gada 1. jūlijs5 |
| 19980424 | 1998. gada 24. aprīlis |
| 28.02.2014 | 2014. gada 28. februāris |
Kad Excel ir novērtējis datuma vērtību kā tekstu, viena no iespējām ir izmantot formulu, lai tekstu parsētu tā komponentos (gads, mēnesis, diena), un pēc tam tos izveidotu datumam ar funkciju DATE. Kā minēts iepriekš, pirms formulas izmantošanas iesaku vispirms izmēģināt zemāk minētos risinājumus (pievienojot nulli un kolonnām izmantojot tekstu). Abi risinājumi ir ātrāki un prasa mazāk pūļu.
Paskaidrojums
Funkcija DATE izveido derīgu datumu, izmantojot trīs argumentus: gads, mēnesis un diena:
=DATE(year,month,day)
Šūnā C6 mēs izmantojam funkcijas LEFT, MID un RIGHT, lai katru no šiem komponentiem izvilktu no teksta virknes un rezultātus ievadītu funkcijā DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkcija LEFT izraksta kreisās 4 rakstzīmes gadam, MID funkcija - rakstus 5-6. Pozīcijā mēnesim, un funkcija RIGHT - divas labākās rakstzīmes kā dienu. Katrs rezultāts tiek tieši atgriezts funkcijā DATE. Gala rezultāts ir pareizs Excel datums, kuru var formatēt jebkurā jums vēlamajā veidā.
Šo pieeju pēc vajadzības var pielāgot. Piemēram, neatpazīts datuma formāts 8. rindā ir dd.mm.yyyy un formula C8 ir:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Garas formas teksts
Dažreiz datumi var būt garākā formā, piemēram, “2020. gada 11. aprīlis, 08:43:13”, kurus Excel neatpazīst pareizi. Šajā gadījumā jūs, iespējams, varēsit pielāgot virkni tā, lai programma Excel varētu pareizi atpazīt datumu, izmantojot funkciju SUBSTITUTE. Zemāk esošā formula atstarpes otro gadījumu ("") aizstāj ar komatu un atstarpi (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Kad mēs pievienosim komatu aiz mēneša nosaukuma, Excel sapratīs datumu, taču tam joprojām ir nepieciešams neliels "sitiens". Tāpēc beigās pievienojam nulli. Matemātikas darbība liek programmai Excel mēģināt pārveidot virkni par skaitli. Ja tas būs veiksmīgs, tiks iegūts derīgs Excel datums. Ņemiet vērā, ka datuma pareizai parādīšanai, iespējams, būs jāpiemēro datuma numura formatējums.
Bez formulām
Pirms izmantojat formulu, lai manuāli parsētu un izveidotu datumu no teksta, izmēģiniet kādu no tālāk norādītajiem labojumiem. Pirmajā opcijā tiek izmantota matemātikas operācija, lai nedaudz "pakustinātu" programmu Excel un piespiestu to izmēģināt un novērtēt tekstu kā skaitli. Tā kā Excel datumi patiesībā ir skaitļi, tas bieži vien var padarīt šo triku. Jums var būt nepieciešams piemērot datuma formātu, ja operācijas izdodas.
Pievienojiet nulli, lai labotu datumus
Dažreiz datumus var sastapt teksta formātā, kas Excel jāatpazīst. Šajā gadījumā jūs, iespējams, varēsit piespiest programmu Excel konvertēt teksta vērtības datumos, vērtībai pievienojot nulli. Kad jūs pievienojat nulli, Excel mēģinās piespiest teksta vērtības skaitļiem. Tā kā datumi ir tikai skaitļi, šis triks ir lielisks veids, kā konvertēt datumus teksta formātā, kas Excel patiešām būtu jāsaprot.
Lai konvertētu datumus, pievienojot nulli, mēģiniet ielīmēt īpašo:
- Ievadiet nulli (0) neizmantotā šūnā un kopējiet uz starpliktuvi
- Atlasiet problemātiskos datumus
- Īpaša ielīmēšana> Vērtības> Pievienot
- Lietot datuma formātu (ja nepieciešams)
Šādā formulā varat pievienot arī nulli:
=A1+0
kur A1 satur neatpazītu datumu.
Teksts kolonnās, lai labotu datumus
Vēl viens veids, kā panākt, lai Excel atpazītu datumus, ir izmantot funkciju Teksts kolonnām:
Atlasiet datumu kolonnu un pēc tam mēģiniet Dati> Teksts kolonnās> Fiksēts> Pabeigt
Ja Excel atpazīs datumus, tos visus salabos vienā solī.
