Tekstina vormindatud kuupäevade tegelikeks kuupäevadeks teisendamiseks Excelis on palju võimalusi ja selle õpetuse eesmärk on neid kõiki uurida.
Kui impordite andmeid Excelisse, võivad need esineda mitmel erineval kujul, mida Excel ei tuvasta. Mõnikord, kui kuupäevad imporditakse välisest allikast, on need vormindatud tekstina.
Samuti on oluline teada, et Excel rakendab kuupäevavorminguid teie süsteemi piirkonna sätete alusel. Seega, kui impordite Excelisse teisest riigist pärit andmeid, ei pruugi Excel neid tuvastada ega salvestada neid tekstikirjetena.
Kui teil on see probleem, saate teksti Excelis kuupäevaks teisendamiseks mitmel viisil kasutada ja me käsitleme neid kõiki selles õpetuses.
Meetodid teksti teisendamiseks kuupäevaks
Kui teie tööleht sisaldab tegelike kuupäevade asemel tekstina vormindatud kuupäevi, ei saa neid arvutustes kasutada. Seega peate need tegelikeks kuupäevadeks tagasi teisendama.
Kui näete kuupäevi vasakule joondatud, tähendab see, et need on vormindatud tekstina, kuna tekstid on vaikimisi vasakule joondatud. Ja number ja kuupäevad on alati paremale joondatud.
Teksti saab Excelis kuupäevaks teisendada mitmel erineval viisil. Need on järgmised:
- Veakontrolli valik
- Exceli teksti veergudeks funktsioon
- Otsi ja asenda
- Kleebi spetsiaalne tööriist
- Exceli valem ja funktsioonid
Teisendage tekst kuupäevaks, kasutades veakontrolli valikut
Excelil on sisseehitatud veakontrolli funktsioon, mis tuvastab teie andmetes ilmsed vead. Kui see leiab vea, kuvab see lahtri vasakus ülanurgas väikese rohelise kolmnurga (vea indikaator), milles viga on. Kui valite selle lahtri, kuvatakse kollase hüüumärgiga hoiatusmärk. Kui liigutate kursori selle märgi kohale, teavitab Excel teid selle lahtri võimalikust probleemist.
Näiteks kui sisestate kuupäeva kahekohalises vormingus aasta, eeldab Excel selle kuupäeva tekstina ja salvestab selle tekstina. Ja kui valite selle lahtri, ilmub hüüumärk koos hoiatusega: "See lahter sisaldab kuupäevastringi, mis koosneb ainult kahest aastanumbrist".
Kui teie lahtrid näitavad seda veaindikaatorit, klõpsake hüüumärki ja kuvatakse mõned valikud tekstina vormindatud kuupäevade teisendamiseks tegelikeks kuupäevadeks. Excel näitab teile selle teisendamiseks 19XX või 20XX (19XX 1915, 20XX 2015). Valige sobiv valik.
Seejärel teisendatakse tekst õigesse kuupäevavormingusse.
Kuidas lubada Excelis veakontrolli suvand
Tavaliselt on suvand Error Checking Excelis vaikimisi sisse lülitatud. Kui veakontrolli funktsioon teie jaoks ei tööta, peate lubama Excelis veakontrolli.
Selleks klõpsake vahekaarti "Fail" ja valige vasakpoolsest paneelist "Valikud".
Klõpsake Exceli suvandite akna vasakpoolsel paneelil 'Valemid' ja parempoolsel paneelil lubage jaotises Error Checking 'Luba taustatõrkekontroll'. Ja märkige jaotises Veakontrolli reeglid linnuke „Kahekohalise aastaarvuga lahtrid”.
Teisendage tekst kuupäevaks, kasutades Exceli teksti veeru funktsiooni
Tekst veeruks on Exceli suurepärane funktsioon, mis võimaldab jagada andmeid mitmeks veerguks, ning samuti on see võimas tööriist tekstiväärtuste teisendamiseks kuupäevaväärtusteks. See meetod tuvastab mitu erinevat andmevormingut ja teisendab need õigesse kuupäevavormingusse.
Lihtsate tekstistringide teisendamine kuupäevadeks
Oletame, et teie kuupäevad on vormindatud järgmistes tekstistringides:
Saate kasutada teksti veergude viisardit, et vormindada need kiiresti uuesti kuupäevadesse.
Esmalt valige tekstikirjete vahemik, mille soovite kuupäevadeks teisendada. Seejärel minge lindi vahekaardile "Andmed" ja klõpsake rühmas Andmetööriistad valikut "Tekst veergudesse".
Ilmub teksti veergude viisard. Valige tekstist veergu viisardi 1. sammus jaotises Algne andmetüüp valik Piiratud ja klõpsake nuppu Edasi.
2. sammus tühjendage kõik ruudud „Eraldajad” ja klõpsake nuppu Edasi.
Viisardi viimases etapis valige veeru andmevorming alt "Kuupäev" ja valige "Kuupäevad" kõrval olevast ripploendist kuupäevavorming ja klõpsake nuppu "Lõpeta". Meie puhul teisendame teksti kuupäevad, mis on esitatud kui "01 02 1995" (päev kuu, aasta), seega valime ripploendist "Kuupäev:" "DMY".
Nüüd teisendab Excel teie tekstikuupäevad tegelikeks kuupäevadeks ja kuvab need lahtrites paremale joondatud kujul.
Märge: Enne funktsiooni Text to Column kasutamise alustamist veenduge, et kõik teie tekstistringid oleksid identses vormingus, vastasel juhul tekste ei teisendata. Näiteks kui mõned teie tekstikuupäevad on vormindatud kuu/päeva/aasta (MDY) vormingus, teised aga päeva/kuu/aasta (DMY) vormingus, ja kui valite 3. sammus „DMY”, saate valesid tulemusi. Nagu on näidatud alloleval pildil.
Keerulise teksti stringi teisendamine kuupäevaks
Funktsioon Tekst veergudeks on kasulik, kui soovite teisendada keerulised tekstistringid kuupäevadeks. See võimaldab teil kasutada eraldajaid, et teha kindlaks, kus teie andmed tuleks jagada ja kuvada kahes või enamas veerus. Ja ühendage kuupäevade poolitatud osad terveks kuupäevaks, kasutades funktsiooni DATE.
Näiteks kui teie kuupäevad kuvatakse mitmeosaliste tekstistringidena, näiteks järgmiselt:
Kolmapäeval, 01. veebruaril 2020
01. veebruar 2020, kell 16.10
Saate kasutada teksti veergu viisardit, et eraldada päeva-, kuupäeva- ja kellaaja teave, mis on eraldatud komadega, ning kuvada need mitmes veerus.
Esiteks valige kõik tekstistringid, mida soovite kuupäevadeks teisendada. Klõpsake vahekaardil Andmed nuppu "Tekst veergudesse". Valige tekstist veergu viisardi 1. sammus jaotises Algne andmetüüp valik Piiratud ja klõpsake nuppu Edasi.
Valige viisardi 2. sammus eraldusmärgid, mida teie tekstistringid sisaldavad, ja klõpsake nuppu Edasi. Meie näidisteksti stringid on eraldatud koma ja tühikuga – "esmaspäev, veebruar 01, 2015, 13:00". Tekstistringide mitmeks veerguks jagamiseks peaksime valima eraldajateks "koma" ja "tühikud".
Viimases etapis valige jaotises Andmete eelvaade kõigi veergude jaoks vorming „Üldine”. Määrake, kuhu veerud väljale „Sihtkoht” sisestada, kui te seda ei tee, kirjutab see algandmed üle. Kui soovite mõnda osa algandmetest ignoreerida, klõpsake sellel jaotises Andmete eelvaade ja valige suvand „Ära impordi veergu (vahele)”. Seejärel klõpsake nuppu "Lõpeta".
Nüüd on kuupäevade osad (nädalapäevad, kuu, aasta, kellaaeg) jagatud veergudeks B, C, D, E, F ja G.
Seejärel ühendage kuupäevaosad kokku valemi DATE abil, et saada kogu kuupäev.
Exceli funktsiooni DATE süntaks:
=KUUPÄEV(aasta,kuu,päev)Meie näites on kuu, päeva ja aasta osad vastavalt veergudes C, D ja E.
Funktsioon DATE tuvastab ainult numbreid, mitte teksti. Kuna meie kuu väärtused veerus C on kõik tekstistringid, peame need teisendama numbriteks. Selleks tuleb funktsiooni KUU abil muuta kuu nimi kuu numbriks.
Kuu nime kuunumbriks teisendamiseks kasutage funktsiooni DATE sees funktsiooni KUU:
=KUUD(1&C1)Funktsioon KUUD lisab 1 lahtrisse C2, mis sisaldab kuu nime, et teisendada kuu nimi vastavaks kuunumbriks.
See on funktsioon DATE, mida peame kasutama erinevate veergude kuupäevaosade kombineerimiseks:
=KUUPÄEV(E1,KUUD(1&C1),D1)
Nüüd kasutage valemilahtri allnurgas olevat täitekäepidet ja rakendage valem veerule.
Teisendage tekst kuupäevaks, kasutades otsimis- ja asendamismeetodit
See meetod kasutab teksti vormingu muutmiseks kuupäevadeks eraldajaid. Kui päev, kuu ja aasta on teie kuupäevades eraldatud mõne muu eraldusmärgiga peale sidekriipsu (-) või kaldkriipsu (/), ei tuvasta Excel neid kuupäevadena ja salvestab need tekstina.
Selle probleemi lahendamiseks kasutage funktsiooni Otsi ja asenda. Kui muudate ebastandardseid perioodieraldajaid (.) kaldkriipsudega (/) või sidekriipsuga (-), muudab Excel väärtused automaatselt kuupäevadeks.
Esmalt valige kõik tekstikuupäevad, mida soovite kuupäevadeks teisendada. Klõpsake vahekaardil „Kodu“ lindi kõige paremas nurgas nuppu „Otsi ja vali“ ja valige „Asenda“. Teise võimalusena vajutage Ctrl+H Otsi ja asenda dialoogiboksi avamiseks.
Tippige dialoogiboksi Otsi ja asenda eraldusmärk, mida teie tekst sisaldab (meie puhul punkt (.) väljale „Otsi mis” ja kaldkriips (/) või sidekriips (-) väljale „Asenda” . Eraldajate asendamiseks klõpsake nuppu "Asenda kõik" ja akna sulgemiseks klõpsake nuppu "Sule".
Nüüd tunneb Excel ära, et teie tekstistringid on nüüd kuupäevad ja vormindab need automaatselt kuupäevadena. Teie kuupäevad joondatakse paremale, nagu allpool näidatud.
Teisendage tekst kuupäevaks spetsiaalse tööriista Kleebi abil
Teine kiire ja lihtne viis tekstistringide kuupäevadeks teisendamiseks on lisada tekstistringile 0, kasutades spetsiaalset kleepimisvalikut. Nulli lisamine väärtusele teisendab teksti kuupäeva seerianumbriks, mida saate kuupäevana vormindada.
Esmalt valige tühi lahter ja kopeerige see (valige see ja vajutage Ctrl + C kopeerida).
Seejärel valige lahtrid, mis sisaldavad tekstikuupäevi, mida soovite teisendada, paremklõpsake ja valige suvand "Kleebi spetsiaalselt".
Valige dialoogiaknas Paste Special (Kleebi eriline) jaotises Kleebi „Kõik”, valige jaotises Toiming „Lisa” ja klõpsake nuppu „OK”.
Saate teha ka muid aritmeetilisi toiminguid sihtlahtri väärtuse lahutamiseks/korrutamiseks/jagamiseks kleebitud väärtusega (nt lahtrite korrutamine 1-ga või 1-ga jagamine või nulli lahutamine).
Kui valite toimingus "Lisa", lisab see kõigile valitud tekstikuupäevadele "null", kuna "0" lisamine ei muuda väärtusi, saate iga kuupäeva jaoks seerianumbri. Nüüd peate vaid muutma lahtrite vormingut.
Valige seerianumbrid ja klõpsake vahekaardil "Kodu" numbrirühma ripploendil "Numbrivorming". Valige rippmenüüst "Lühike kuupäev".
Nagu näete nüüd, on numbrid vormindatud kuupäevadena ja joondatud paremale.
Teisendage tekst valemite abil kuupäevaks
Teksti kuupäevaks teisendamiseks kasutatakse peamiselt kahte funktsiooni: DATEVALUE ja VALUE.
Exceli funktsiooni DATEVALUE kasutamine
Exceli funktsioon DATEVALUE on üks lihtsamaid viise tekstina esitatud kuupäeva teisendamiseks kuupäeva seerianumbriks.
Funktsiooni DATEVALUE süntaks:
=DATEVALUE(kuupäeva_tekst)Argument: kuupäeva_tekst määrab tekstistringi, mida soovite varjata või viidata tekstikuupäevi sisaldavale lahtrile.
Valem:
=KUUPÄEVVÄÄRTUS(A1)Järgmine pilt illustreerib, kuidas funktsioon DATEVALUE käsitleb mõnda erinevat kuupäevavormingut, mis salvestatakse tekstina.
Teil on kuupäeva seerianumbrid, nüüd peate nendele numbritele rakendama kuupäeva vormingu. Selleks valige seerianumbritega lahtrid, seejärel minge vahekaardile "Kodu" ja valige ripploendist "Numbrivorming" "Lühike kuupäev".
Nüüd on teil vormindatud kuupäevad veerus C.
Isegi kui teie tekstikuupäevas (A8) pole aastaosa, kasutab DATEVALUE teie arvuti kella järgi jooksvat aastat.
Funktsioon DATEVALUE teisendab ainult need tekstiväärtused, mis näevad välja nagu kuupäev. See ei saa numbrit meenutavat teksti kuupäevaks teisendada ega numbri väärtust kuupäevaks muuta, selleks on vaja Exceli funktsiooni VALUE.
Exceli funktsiooni VALUE kasutamine
Funktsioon Exceli VALUE suudab teisendada mis tahes kuupäeva või numbrit meenutava tekstistringi numbriliseks väärtuseks, seega on see tõesti abiks suvalise arvu, mitte ainult kuupäevade teisendamiseks.
Funktsioon VALUE:
=VÄÄRTUS(tekst)tekst– tekstistring, mida tahame teisendada, või viide tekstistringi sisaldavale lahtrile.
Näidisvalem teksti kuupäeva teisendamiseks:
=VÄÄRTUS(A1)Allpool olev valem VALUE võib muuta mis tahes kuupäevana näivad tekstistringid numbriteks, nagu allpool näidatud.
Funktsioon VALUE ei toeta aga igat tüüpi kuupäevaväärtusi. Näiteks kui kuupäevad kasutavad kümnendkohti (A11), tagastab see väärtuse #VALUE! viga.
Kui teil on kuupäeva jaoks seerianumber, peate vormindama lahtri kuupäeva seerianumbriga, et see näeks välja nagu kuupäev, nagu oleme teinud funktsiooni DATEVALUE puhul. Selleks valige seerianumbrid ja vahekaardi "Kodu" rippmenüüst "Numbrivorming" valik "Kuupäev".
See on kõik, need on 5 erinevat viisi, kuidas saate tekstina vormindatud kuupäevi Excelis kuupäevadeks teisendada.