![](https://cdn.wiki-base.com/9810662/excel_formula_convert_text_to_date__2.png.webp)
Formuła ogólna
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Podsumowanie
Aby przekonwertować tekst w nierozpoznanym formacie daty na prawidłową datę w programie Excel, można przeanalizować tekst i zebrać odpowiednią datę za pomocą formuły opartej na kilku funkcjach: DATA, LEWO, ŚRODEK i PRAWO. W pokazanym przykładzie formuła w C6 to:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Ta formuła wyodrębnia osobno wartości roku, miesiąca i dnia i używa funkcji DATA, aby zebrać je w datę 24 października 2000 r.
tło
Podczas pracy z danymi z innego systemu możesz napotkać sytuację, w której daty nie są prawidłowo rozpoznawane przez program Excel, który zamiast tego traktuje daty jak tekst. Na przykład możesz mieć takie wartości tekstowe:
Tekst | Przedstawiona data |
20001024 | 24 października 2000 |
20050701 | 1 lipca 2011 5 |
19980424 | 24 kwietnia 1998 |
28.02.2014 | 28 lutego 2014 |
Gdy program Excel ocenił wartość daty jako tekst, jedną z opcji jest użycie formuły do przeanalizowania tekstu na jego składniki (rok, miesiąc, dzień) i użycie ich do utworzenia daty za pomocą funkcji DATA. Jak wspomniano powyżej, zalecamy najpierw wypróbowanie poniższych rozwiązań (dodanie zera i użycie tekstu w kolumnach) przed użyciem formuły. Oba rozwiązania są szybsze i wymagają mniejszego wysiłku.
Wyjaśnienie
Funkcja DATA tworzy prawidłową datę przy użyciu trzech argumentów: roku, miesiąca i dnia:
=DATE(year,month,day)
W komórce C6 używamy funkcji LEFT, MID i RIGHT, aby wyodrębnić każdy z tych składników z ciągu tekstowego i przekazać wyniki do funkcji DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkcja LEFT wyodrębnia 4 skrajne lewe znaki na rok, funkcja MID wyodrębnia znaki na pozycjach 5-6 dla miesiąca, a funkcja RIGHT wyodrębnia 2 skrajne prawe znaki jako dzień. Każdy wynik jest zwracany bezpośrednio do funkcji DATA. Ostatecznym wynikiem jest odpowiednia data w Excelu, którą można sformatować w dowolny sposób.
To podejście można dostosować w razie potrzeby. Na przykład nierozpoznany format daty w wierszu 8 to dd.mm.rrrr, a formuła w C8 to:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Długi tekst
Czasami możesz mieć daty w dłuższej formie, na przykład „Apr 11 2020 08:43:13”, których program Excel nie rozpoznaje poprawnie. W takim przypadku możesz dostosować ciąg w sposób, który umożliwi programowi Excel prawidłowe rozpoznanie daty za pomocą funkcji PODSTAW. Poniższy wzór zastępuje drugie wystąpienie spacji („”) przecinkiem i spacją („,”):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Gdy dodamy przecinek po nazwie miesiąca, program Excel zrozumie datę, ale nadal będzie wymagał małego „kopa”. Dlatego na końcu dodajemy zero. Operacja matematyczna powoduje, że program Excel próbuje przekonwertować ciąg na liczbę. Jeśli się powiedzie, da to prawidłową datę w Excelu. Pamiętaj, że może być konieczne zastosowanie formatowania numeru daty, aby data była wyświetlana poprawnie.
Bez formuł
Zanim użyjesz formuły do ręcznego przeanalizowania i skonstruowania daty na podstawie tekstu, wypróbuj jedną z poniższych poprawek. Pierwsza opcja wykorzystuje operację matematyczną, aby nieco „podeprzeć” Excela i zmusić go do próby oceny tekstu jako liczby. Ponieważ daty w programie Excel są w rzeczywistości liczbami, często może to załatwić sprawę. Może być konieczne zastosowanie formatu daty, jeśli operacje się powiodą.
Dodaj zero, aby naprawić daty
Czasami napotkasz daty w formacie tekstowym, który powinien rozpoznawać Excel. W takim przypadku możesz zmusić program Excel do konwersji wartości tekstowych na daty, dodając zero do wartości. Po dodaniu zera program Excel spróbuje przekształcić wartości tekstowe w liczby. Ponieważ daty to tylko liczby, ta sztuczka to świetny sposób na konwersję dat na format tekstowy, który program Excel naprawdę powinien zrozumieć.
Aby przekonwertować daty na miejscu, dodając zero, spróbuj wkleić specjalnie:
- Wpisz zero (0) w nieużywanej komórce i skopiuj do schowka
- Wybierz problematyczne daty
- Wklej specjalnie> Wartości> Dodaj
- Zastosuj format daty (w razie potrzeby)
Możesz również dodać zero w formule takiej jak ta:
=A1+0
gdzie A1 zawiera nierozpoznaną datę.
Tekst do kolumn, aby ustalić daty
Innym sposobem na to, aby program Excel rozpoznawał daty, jest użycie funkcji zamiany tekstu na kolumny:
Wybierz kolumnę dat, a następnie wypróbuj Dane> Tekst do kolumn> Stałe> Zakończ
Jeśli program Excel rozpozna daty, naprawi je wszystkie w jednym kroku.