Üldine valem
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Kokkuvõte
Tunnustamata kuupäevavormingus teksti teisendamiseks õigeks Exceli kuupäevaks saate teksti sõeluda ja õige kuupäeva kokku panna valemiga, mis põhineb mitmel funktsioonil: DATE, LEFT, MID ja RIGHT. Näidatud näites on valem C6-s järgmine:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
See valem eraldab aasta, kuu ja päeva väärtused eraldi ning kasutab kuupäeva 24. oktoober 2000 ühendamiseks funktsiooni DATE.
Taust
Kui töötate teise süsteemi andmetega, võite sattuda olukorda, kus Excel ei tunnista kuupäevi õigesti, mis kohtleb kuupäevi hoopis tekstina. Näiteks võivad teil olla sellised tekstiväärtused:
| Tekst | Esindamise kuupäev |
| 20001024 | 24. oktoober 2000 |
| 20050701 | 1. juuli 2011 |
| 19980424 | 24. aprill 1998 |
| 28.02.2014 | 28. veebruar 2014 |
Kui Excel on kuupäeva väärtust tekstina hinnanud, on üheks võimaluseks kasutada valemit teksti sõelumiseks komponentideks (aasta, kuu, päev) ja kasutada neid kuupäeva tegemiseks funktsiooniga Kuupäev. Nagu eespool märgitud, soovitan enne valemi kasutamist kõigepealt proovida allolevaid lahendusi (lisades nulli ja kasutades veergudele teksti). Mõlemad lahendused on kiiremad ja nõuavad vähem pingutusi.
Selgitus
Funktsioon DATE loob kehtiva kuupäeva, kasutades kolme argumenti: aasta, kuu ja päev:
=DATE(year,month,day)
Lahtris C6 kasutame funktsioone VASAK, MID ja PAREM, et ekstraktida kõik need komponendid tekstistringist ja sisestada tulemused funktsiooni DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funktsioon VASAK ekstraheerib aasta jaoks vasakpoolsemad 4 tähemärki, funktsioon MID ekstraheerib tähemärgid positsioonides 5–6 kuu jooksul ja funktsioon PAREM - kaks paremat tähemärki päevana. Iga tulemus tagastatakse otse funktsiooni DATE. Lõpptulemuseks on korralik Exceli kuupäev, mida saab vormistada mis tahes viisil.
Seda lähenemist saab vastavalt vajadusele kohandada. Näiteks 8. real on tundmatu kuupäevavorming pp.kk.aaaa ja valem C8-s on järgmine:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Pika vormiga tekst
Mõnikord võivad teil olla kuupäevad pikemas vormis, näiteks "11. aprill 2020 08:43:13", mida Excel ei tunnista õigesti. Sellisel juhul saate stringi korrigeerida viisil, mis võimaldab Excelil funktsiooni SUBSTITUTE abil kuupäeva õigesti ära tunda. Allolev valem asendab tühiku teise eksemplari ("") komaga ja tühikuga (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Kui lisame koma kuu nime järele, saab Excel kuupäevast aru, kuid see vajab siiski väikest "lööki". Sellepärast lisame lõpus nulli. Matemaatikaoperatsioon paneb Exceli proovima stringi teisendada numbriks. Eduka tulemuse korral saadakse kehtiv Exceli kuupäev. Pange tähele, et kuupäeva õigeks kuvamiseks peate võib-olla rakendama kuupäeva numbri vormindamist.
Ilma valemiteta
Enne kui kasutate valemi teksti kuupäeva käsitsi sõelumiseks ja koostamiseks, proovige ühte järgmistest parandustest. Esimene variant kasutab matemaatikaoperatsiooni abil Exceli natuke "nihutama" ja sunnib proovima teksti arvuna hinnata. Kuna Exceli kuupäevad on tegelikult numbrid, võib see sageli trikki teha. Kui toimingud õnnestuvad, peate võib-olla rakendama kuupäeva vormingut.
Kuupäevade parandamiseks lisage null
Mõnikord kohtate kuupäevi tekstivormingus, mille Excel peaks ära tundma. Sel juhul võite võib-olla sundida Exceli teksti väärtused kuupäevadeks teisendama, lisades väärtusele nulli. Kui lisate nulli, püüab Excel tekstiväärtusi numbritele sundida. Kuna kuupäevad on vaid numbrid, on see trikk suurepärane võimalus kuupäevade teisendamiseks tekstivormingus, millest Excel tegelikult aru peaks saama.
Kuupäevade teisendamiseks nulli lisamisega proovige Kleebi erilist:
- Sisestage kasutamata lahtrisse null (0) ja kopeerige lõikelauale
- Valige probleemsed kuupäevad
- Kleebi eriline> Väärtused> Lisa
- Kuupäeva vormingu rakendamine (vajadusel)
Sellises valemis saate lisada ka nulli:
=A1+0
kus A1 sisaldab tunnustamata kuupäeva.
Veergude tekst kuupäevade parandamiseks
Teine viis, kuidas Excelis kuupäevi ära tunda, on funktsiooni Tekst veergudesse kasutamine:
Valige kuupäevade veerg ja proovige Andmed> Veergude tekst> Parandatud> Lõpeta
Kui Excel tuvastab kuupäevad, parandab need kõik ühe sammuna.
