Yleinen kaava
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Yhteenveto
Jos haluat muuntaa tunnistamattoman päivämäärämuodon tekstin oikeaan Excel-päivämäärään, voit jäsentää tekstin ja koota oikea päivämäärä kaavalla, joka perustuu useisiin toimintoihin: PÄIVÄYS, VASEN, KESKI ja OIKEA. Esitetyssä esimerkissä kaavassa C6 on:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Tämä kaava poimi vuosi-, kuukausi- ja päiväarvot erikseen ja käyttää DATE-funktiota koottaakseen ne päivämäärään 24. lokakuuta 2000.
Tausta
Kun työskentelet toisen järjestelmän tietojen kanssa, saatat törmätä tilanteeseen, jossa Excel ei tunnista päivämääriä kunnolla, vaan käsittelee päivämääriä tekstinä. Sinulla voi olla esimerkiksi seuraavanlaisia teksti-arvoja:
| Teksti | Edustamispäivä |
| 20001024 | 24. lokakuuta 2000 |
| 20050701 | 1. heinäkuuta 20115 |
| 19980424 | 24. huhtikuuta 1998 |
| 28.02.2014 | 28. helmikuuta 2014 |
Kun Excel on arvioinut päivämääräarvon tekstinä, yksi vaihtoehto on jäsentää teksti kaavaksi komponentteihinsa (vuosi, kuukausi, päivä) kaavan avulla ja tehdä niiden avulla päivämäärä DATE-funktiolla. Kuten edellä todettiin, suosittelen ensin kokeilemaan alla olevia ratkaisuja (lisäämällä nolla ja käyttämällä tekstiä sarakkeisiin) ennen kaavan käyttämistä. Molemmat ongelmat ovat nopeammat ja vaativat vähemmän vaivaa.
Selitys
PÄIVÄYS-funktio luo kelvollisen päivämäärän kolmella argumentilla: vuosi, kuukausi ja päivä:
=DATE(year,month,day)
Solussa C6 käytämme VASEN-, MID- ja OIKEA-funktioita kaikkien näiden komponenttien poimimiseksi tekstimerkkijonosta ja syötämme tulokset PÄIVÄYS-funktioon:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
VASEN-toiminto poimi vasemmanpuoleisimmat 4 merkkiä vuodeksi, MID-toiminto poimii merkit asemissa 5-6 kuukauden ajan ja OIKEA-toiminto poimii oikeanpuoleisimmat 2 merkkiä päivinä. Jokainen tulos palautetaan suoraan DATE-toimintoon. Lopputulos on oikea Excel-päivämäärä, joka voidaan muotoilla haluamallasi tavalla.
Tätä lähestymistapaa voidaan mukauttaa tarpeen mukaan. Esimerkiksi rivillä 8 oleva tuntematon päivämäärämuoto on pp.kk.vvvv ja kaavassa C8 on:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Pitkä muotoinen teksti
Joskus päivämäärät voivat olla pidemmässä muodossa, kuten "11.4.2020 08:43:13", joita Excel ei tunnista kunnolla. Tässä tapauksessa saatat pystyä säätämään merkkijonoa tavalla, joka antaa Excelille mahdollisuuden tunnistaa päivämäärä SUBSTITUTE-toiminnolla. Alla oleva kaava korvaa toisen välilyönnin ("") pilkulla ja välilyönnillä (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Kun olemme lisänneet pilkun kuukauden nimen jälkeen, Excel ymmärtää päivämäärän, mutta se vaatii vielä pienen potkun. Siksi lisätään nolla loppuun. Matemaattinen operaatio saa Excelin yrittämään muuntaa merkkijonon luvuksi. Kun tämä onnistuu, tämä johtaa kelvolliseen Excel-päivämäärään. Huomaa, että sinun on ehkä sovellettava päivämääränumeron muotoilua päivämäärän näyttämiseksi oikein.
Ilman kaavoja
Kokeile jotakin alla olevista korjauksista, ennen kuin käytät kaavaa jäsentämään ja muodostamaan päivämäärä manuaalisesti tekstistä. Ensimmäinen vaihtoehto käyttää matemaattista operaatiota "työntämään" Exceliä hieman ja pakottaa sen yrittämään arvioida tekstiä lukuna. Koska Excel-päivämäärät ovat itse asiassa numeroita, tämä voi usein tehdä temppu. Saatat joutua käyttämään päivämäärämuotoa, jos toiminta onnistuu.
Lisää nolla vahvistaa päivämäärät
Joskus kohtaat päivämäärät tekstimuodossa, jonka Excelin pitäisi tunnistaa. Tässä tapauksessa saatat pystyä pakottamaan Excelin muuntamaan tekstiarvot päivämääriksi lisäämällä arvoon nollan. Kun lisäät nollan, Excel yrittää pakottaa tekstiarvot numeroihin. Koska päivämäärät ovat vain numeroita, tämä temppu on loistava tapa muuntaa päivämäärät tekstimuodossa, jonka Excelin pitäisi todella ymmärtää.
Jos haluat muuntaa päivämäärät paikalleen lisäämällä nollan, kokeile Liitä erityinen:
- Syötä nolla (0) käyttämättömään soluun ja kopioi leikepöydälle
- Valitse ongelmalliset päivämäärät
- Liitä erityinen> Arvot> Lisää
- Käytä päivämäärämuotoa (tarvittaessa)
Voit myös lisätä nollan tällaiseen kaavaan:
=A1+0
missä A1 sisältää tuntemattoman päivämäärän.
Korjaa päivämäärät kirjoittamalla sarakkeisiin tekstiä
Toinen tapa saada Excel tunnistamaan päivämäärät on käyttää Tekstistä sarakkeisiin -ominaisuutta:
Valitse päivämäärän sarake ja kokeile sitten Tiedot> Teksti sarakkeisiin> Kiinteä> Valmis
Jos Excel tunnistaa päivämäärät, se korjaa ne kaikki yhdessä vaiheessa.
