Általános képlet
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Összegzés
A fel nem ismert dátumformátumú szöveg megfelelő Excel dátummá konvertálásához elemezheti a szöveget, és összeállíthatja a megfelelő dátumot egy képlet alapján, amely több függvényen alapul: DÁTUM, BAL, MID és JOBB. A bemutatott példában a C6 képlete a következő:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ez a képlet külön vonja ki az év, a hónap és a nap értékeit, és a DATE függvény segítségével gyűjti össze őket a 2000. október 24-i dátumra.
Háttér
Ha egy másik rendszer adataival dolgozik, akkor olyan helyzetbe kerülhet, amikor a dátumokat az Excel nem ismeri fel megfelelően, és ehelyett a dátumokat szövegként kezeli. Például lehetnek ilyen szövegértékei:
| Szöveg | Képviselet dátuma |
| 20001024 | 2000. október 24 |
| 20050701 | 2011. július 1 |
| 19980424 | 1998. április 24 |
| 2014.02.28 | 2014. február 28 |
Amikor az Excel a dátumértéket szövegként értékelte, az egyik lehetőség az, hogy képlet segítségével elemezzük a szöveget alkotóelemeire (év, hónap, nap), és ezek alapján készítsünk dátumot a DÁTUM funkcióval. Amint azt fentebb megjegyeztem, ajánlom, hogy a képlet használata előtt először próbálja ki az alábbi megoldásokat (nulla hozzáadása és szöveg használata oszlopokhoz). Mindkét megoldás gyorsabb és kevesebb erőfeszítést igényel.
Magyarázat
A DATE függvény érvényes argumentumot hoz létre három argumentum segítségével: év, hónap és nap:
=DATE(year,month,day)
A C6 cellában a LEFT, MID és RIGHT függvényeket használjuk az egyes összetevők kivonására egy szöveges karaktersorozatból, és az eredmények bevitelére a DATE függvénybe:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
A BAL funkció kivonja a bal oldali 4 karaktert az évre, a MID funkció kivonja az 5-6. Minden eredmény közvetlenül a DATE függvénybe kerül. A végeredmény egy megfelelő Excel dátum, amelyet tetszés szerint formázhatunk.
Ez a megközelítés szükség szerint testre szabható. Például a 8. sorban fel nem ismert dátumformátum dd.mm.yyyy és a C8 képlet:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Hosszú formájú szöveg
Előfordulhat, hogy dátumai hosszabb formában vannak, például "2020. április 11., 08:43:13", amelyeket az Excel nem ismer fel megfelelően. Ebben az esetben lehet, hogy úgy módosíthatja a karakterláncot, hogy az Excel helyesen tudja felismerni a dátumot a SUBSTITUTE funkcióval. Az alábbi képlet egy szóköz ("") második példányát vesszővel és szóközzel (",") helyettesíti:
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Miután vesszük a vesszőt a hónap neve után, az Excel megérti a dátumot, de még mindig szüksége van egy kis "rúgásra". Ezért adunk a nullához a végén. A matematikai művelet hatására az Excel megpróbálja konvertálni a karakterláncot számgá. Sikeres eredmény esetén érvényes Excel dátumot kap. Ne feledje, hogy a dátum helyes megjelenítéséhez szükség lehet a dátumszám formázására.
Képletek nélkül
Mielőtt képletet használna a dátum kézi elemzésére és szövegből való összeállítására, próbálkozzon az alábbi javítások egyikével. Az első lehetőség egy matematikai művelet segítségével kissé "elmozdítja" az Excel programot, és arra kényszeríti, hogy megpróbálja számként értékelni a szöveget. Mivel az Excel dátumok valójában számok, ez gyakran meg tudja csinálni a trükköt. Lehetséges, hogy dátumformátumot kell alkalmaznia, ha a műveletek sikeresek.
Adjon nulla értéket a dátumok javításához
Előfordul, hogy olyan dátumokkal találkozik a dátumokkal, amelyeket az Excelnek fel kell ismernie. Ebben az esetben előfordulhat, hogy arra kényszerítheti az Excel-t, hogy a szöveges értékeket dátumokká alakítsa, nulla érték hozzáadásával. Ha nullát ad hozzá, az Excel megpróbálja a szöveges értékeket számokra kényszeríteni. Mivel a dátumok csak számok, ez a trükk nagyszerű módja a dátumok konvertálásának olyan szöveges formátumban, amelyet az Excelnek valóban meg kell értenie.
A dátumok nulla hozzáadásával történő konvertálásához próbálkozzon a Speciális beillesztéssel:
- Írja be a nullát (0) egy fel nem használt cellába, és másolja a vágólapra
- Válassza ki a problémás dátumokat
- Különleges beillesztés> Értékek> Hozzáadás
- Dátumformátum alkalmazása (ha szükséges)
Nulla hozzáadható egy ilyen képlethez is:
=A1+0
ahol az A1 egy el nem ismert dátumot tartalmaz.
Szöveg oszlopokba rögzíteni a dátumokat
Egy másik módja annak, hogy az Excel felismerje a dátumokat, a Szöveg oszlopokba funkció használata:
Válassza ki a dátum oszlopot, majd próbálja meg az Adatok> Szöveg oszlopokba> Javítva> Befejezés lehetőséget
Ha az Excel felismeri a dátumokat, mindet egy lépésben kijavítja.
