On tavallista, että tietoja tuodaan muista lähteistä Excelissä, ja on myös tavallista, että tietoja ei ole muotoiltu oikein. Esimerkiksi kun saamme tietoja muista lähteistä, päivämäärä, aika ja numeeriset arvot ovat esimerkiksi tekstimuodossa. Ne on muunnettava oikeassa muodossa. Numerot on helppo muuntaa Excel -numeroiksi. Mutta merkkijonon muuntaminen Excel -päivämäärään voi vaatia hieman vaivaa.
Tässä artikkelissa opimme, miten voit muuntaa tekstin muotoillun päivämäärän ja kellonajan Excel -päivämääränä ja -kellonaikana.
Yleinen kaava
| =DATEVALUE(VASEN(Datetime_string, 10)) + AIKAARVO(MID(Datetime_string, 12,8)) |
Datetime_string: Tämä on päivämäärän ja kellonajan sisältävän merkkijonon viite.
Tämän yleisen kaavan päätoiminnot ovat DATEVALUE ja AIKAARVO. Muita toimintoja käytetään päivämäärämerkkijonon ja aikajonon poimimiseen, jos ne yhdistetään yhdeksi merkkijonoksi. Ja kyllä, solu, joka sisältää tämän kaavan, on muotoiltava päivämäärän muotoon, muuten näet sarjanumerot, jotka voivat hämmentää sinua.
Katsotaanpa joitain esimerkkejä selventämään asioita.
Esimerkki: Muunna tuodun päivämäärän ja kellonajan Excel -päivämääräksi
Tässä päivämäärä ja aika yhdistetään yhdeksi merkkijonoksi. Meidän on otettava päivämäärä ja kellonaika erikseen ja muunnettava ne Excel -ajan ja ajankohdaksi.
Huomaa, että olen muotoillut C3 -päivämäärän niin, että saamani tulokset muunnetaan päivämääriksi enkä näe murto -osaa. (Excelin päivämäärän ja kellonajan salaisuudet).
Joten kirjoitetaan kaava:
| =DATEVALUE(VASEN(B3,10)) + AIKAARVO(MID(B3,12,8)) |
Ja se palauttaa päivämäärän ja kellonajan, jonka Excel pitää datetime -ajankohtana, ei merkkijonona.
Jos et muuta solun muotoilua, tulos näyttää tältä.
Tämä ei ole väärin. Se on sarjanumero, joka edustaa kyseistä päivämäärää ja kellonaikaa. Jos et tiedä miten päivämäärä ja kellonaika näkyvät Excelissä, saat täyden selvyyden täältä.
Kuinka se toimii?
Tämä on yksinkertaista. Käytämme uuttotekniikoita päivämäärän ja kellonajan merkkijonojen poimimiseen. Tässä meillä on VASEN (B3,10). Tämä palauttaa 10 merkkiä B3: n merkkijonon vasemmalta puolelta. Joka on "12.02.2020". Tämä merkkijono syötetään DATEVALUE -funktiolle. Ja DATEVALUE muuntaa päivämäärämerkkijonon arvoonsa. Näin saamme numeron 44167.
Samoin saamme aika -arvon käyttämällä MID -toimintoa (louhintaan) ja TIMEVALUE -funktiota.
MID -funktio palauttaa 8 merkkiä merkkijonon 12. sijaista. Se on "12:23:00". Tämä merkkijono toimitetaan TIMEVALUE -funktiolle, joka palauttaa arvon .51597.
Lopuksi lisäämme nämä luvut yhteen. Tämä antaa meille numeron 44167.51597. Kun muunnamme solun muotoilun muotoon KK/PP/VVVV HH: MM AM/PM, saamme tarkan päivämäärän ja ajan.
Yllä olevassa esimerkissä päivämäärä ja kellonaika yhdistettiin merkkijonoksi. Jos päivämäärä on vain merkkijono ilman ylimääräistä tekstiä tai merkkijonoa, voit käyttää PÄIVÄMÄÄRÄ -funktiota yksinomaan päivämäärän muuntamiseen Excel -päivämäärään.
| =DATEVALUE(B3) |
Jos sinulla on vain aikaa merkkijonona, käytä AIKAARVOtoiminto
| =AIKAARVO(B3) |
Voit lukea näistä toiminnoista napsauttamalla funktion nimeä kaavassa.
Joten kyllä kaverit, tällä tavalla muunnat päivämäärä- ja aikajonon Excel -päivämäärä -aika -arvoksi. Toivottavasti tästä oli apua. Jos sinulla on epäilyksiä tästä artikkelista tai sinulla on muita Excel/VBA -kyselyihin liittyviä kysymyksiä, kysy minua alla olevasta kommenttiosasta. Autan sinua mielelläni.
Muunna päivämäärä ja aika GMT: stä CST: ksi : Kun työskentelemme Excel -raporttien ja Excel -hallintapaneelien kanssa Microsoft Excelissä, meidän on muutettava päivämäärä ja kellonaika. Lisäksi meidän on saatava ero Microsoft Excelin ajoituksista.
Kuinka muuntaa aika desimaaliksi Excelissä : Meidän on muutettava aikaluvut desimaaleiksi Excel 2016: n laskentataulukon avulla. Voimme manipuloida aikaa Excelissä 2010, 2013 ja 2016 käyttämällä funktiota CONVERT, HOUR ja MINUTE.
Kuinka muuntaa päivämäärä tekstiksi Excelissä : Tässä artikkelissa opimme, miten teksti muutetaan päivämääräksi, mutta miten Excel -päivämäärä muutetaan tekstiksi. Excel -päivämäärän muuntamiseksi tekstiksi meillä on muutamia tekniikoita.
Kuukauden nimen muuntaminen numeroksi Microsoft Excelissä : Raporttien osalta numerot ovat parempia kuin teksti. Excelissä, jos haluat muuntaa kuukausien nimet numeroiksi (1-12). Toiminnon DATEVALUE ja MONTH avulla voit helposti muuntaa kuukaudet numeroiksi.
Excel muuntaa desimaalin sekunnit aikamuotoon : Kuten tiedämme, aikaa Excelissä käsitellään numeroina. Tunnit, minuutit ja sekunnit käsitellään desimaalilukuna. Joten kun meillä on sekunteja numeroina, miten muutamme ne aikamuotoon? Tämä artikkeli on käsitelty.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Excel VLOOKUP -toiminnon käyttäminen| Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
Excelin käyttäminen COUNTIF -toiminto| Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.