Excelis on tavaline importida andmeid muudest allikatest ja samuti on tavaline, et ei saada korralikult vormindatud andmeid. Näiteks kui enamasti saame andmeid muudest allikatest, on kuupäev, kellaaeg ja numbrilised väärtused tekstivormingus. Need tuleb teisendada õiges vormingus. Numbreid on lihtne teisendada Exceli numbriteks. Kuid stringi teisendamine Exceli kuupäevaks võib vajada natuke pingutust.
Selles artiklis õpime, kuidas saate Exceli kuupäeva ja kellaaja tekstivormingus kuupäeva ja kellaaja teisendada.
Üldine valem
| =DATEVALUE(VASAK(Datetime_string, 10)) + TIMEVALUE(MID(Datetime_string, 12,8)) |
Datetime_string: See on kuupäeva ja kellaaega sisaldava stringi viide.
Selle üldvalemi põhifunktsioonid on DATEVALUE ja TIMEVALUE. Kuupäevastringi ja ajastringi eraldamiseks kasutatakse muid funktsioone, kui need on ühendatud üheks stringiks. Ja jah, seda valemit sisaldav lahter tuleks vormindada kuupäeva ja aja vormingusse, vastasel juhul näete seerianumbreid, mis võivad teid segadusse ajada.
Vaatame mõningaid näiteid, et asjad selgeks teha.
Näide: teisendage imporditud kuupäev ja kellaaeg Exceli kuupäevaks
Siin on kuupäev ja kellaaeg ühendatud üheks stringiks. Peame eraldama kuupäeva ja kellaaja eraldi ning teisendama need Exceli kuupäeva- ja kellaajaks.
Pange tähele, et olen vormindanud C3 kuupäevaks, nii et saadud tulemused teisendatakse kuupäevadeks ja ma ei näe murdosa. (Exceli kuupäeva ja kellaaja saladused).
Nii et kirjutame valemi:
| =DATEVALUE(VASAK(B3,10)) + TIMEVALUE(MID(B3,12,8)) |
Ja see tagastab kuupäeva ja kellaaja, mida Excel peab kuupäeva- ja kellaajaks, mitte stringiks.
Kui te ei muuda lahtri vormingut, näeb teie tulemus välja selline.
See pole vale. Seda kuupäeva ja kellaaega tähistab seerianumber. Kui te ei tea, kuidas Excel tajub kuupäeva ja kellaaega, saate täieliku selguse siit.
Kuidas see töötab?
See on lihtne. Kuupäeva ja kellaaja stringide ekstraheerimiseks kasutame ekstraheerimistehnikaid. Siin on meil VASAK (B3,10). See tagastab B3 stringist vasakult 10 märki. Mis on "12.02.2020". Nüüd esitatakse see string funktsioonile DATEVALUE. Ja DATEVALUE teisendab kuupäevastringi väärtuseks. Seega saame 44167.
Sarnaselt saame aja väärtuse, kasutades funktsiooni MID (ekstraheerimiseks) ja funktsiooni TIMEVALUE.
Funktsioon MID tagastab stringi 12. positsioonilt 8 märki. Mis on "12:23:00". See string esitatakse funktsioonile TIMEVALUE, mis tagastab .51597.
Lõpuks liidame need arvud kokku. See annab meile numbri 44167.51597. Kui teisendame lahtri vormindamise vorminguks KK/PP/AAAA HH: MM AM/PM, saame täpse kuupäeva ja kellaaja.
Ülaltoodud näites ühendasime kuupäeva ja kellaaja stringina. Kui teil on lihtsalt kuupäev stringina ilma täiendava teksti või stringita, saate kuupäeva teisendamiseks Exceli kuupäevaks kasutada ainult funktsiooni DATEVALUE.
| =DATEVALUE(B3) |
Kui teil on stringina aega, kasutage TIMEVALUEfunktsiooni
| =TIMEVALUE(B3) |
Nende funktsioonide kohta saate lugeda, klõpsates valemis funktsiooni nimel.
Nii et jah, poisid, nii teisendate kuupäeva ja kellaaja stringi Exceli kuupäeva ja kellaaja väärtuseks. Loodan, et sellest oli abi. Kui teil on selle artikli osas kahtlusi või teil on mõni muu päringuga seotud Excel/VBA, küsige seda allolevas kommentaaride osas. Aitan teid hea meelega.
Teisenda kuupäev ja kellaaeg GMT -st CST -ks : Töötades Microsoft Exceli Exceli aruannete ja Exceli juhtpaneelidega, peame teisendama kuupäeva ja kellaaja. Lisaks peame Microsoft Excelis saama ajastuste erinevuse.
Kuidas Excelis aega kümnendkohaks teisendada : Peame ajaarvud teisendama kümnendkohtadeks, kasutades Excelis arvutustabelit. Excelis 2010, 2013 ja 2016 saame ajaga manipuleerida, kasutades funktsiooni CONVERT, HOUR and MINUTE.
Kuidas teisendada kuupäev tekstiks Excelis : Selles artiklis õppisime, kuidas teksti kuupäevaks teisendada, kuid kuidas teisendada Exceli kuupäev tekstiks. Exceli kuupäeva tekstiks teisendamiseks on meil mõned tehnikad.
Kuu nime teisendamine Microsoft Excelis arvuks : Aruannete osas on numbrid paremad kui tekst. Excelis, kui soovite teisendada kuude nimed numbriteks (1–12). Funktsiooni DATEVALUE ja MONTH abil saate hõlpsalt teisendada kuud numbriteks.
Excel teisendab kümnendsekundid ajavormingusse : Nagu me teame, käsitletakse Excelis aega numbrina. Tunde, minuteid ja sekundeid käsitletakse kümnendarvudena. Niisiis, kui meil on sekundit kui numbreid, kuidas teisendada need ajavormingusse? See artikkel sai selle käsitletud.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enimkasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.