Często importuje się dane z innych źródeł w programie Excel, a także często nie otrzymuje się prawidłowo sformatowanych danych. Na przykład przez większość czasu, gdy otrzymujemy dane z innych źródeł, data, godzina i wartości liczbowe są w formacie tekstowym. Muszą być przekonwertowane w odpowiednim formacie. Łatwo jest przekonwertować liczby na liczby w programie Excel. Ale konwersja ciągu na datę i godzinę w programie Excel może wymagać trochę wysiłku.
W tym artykule dowiemy się, jak przekonwertować datę i godzinę w formacie tekstowym na datę i godzinę w programie Excel.
Formuła ogólna
| =DATA.WARTOŚĆ(LEWO(Datetime_string,10)) + WARTOŚĆ CZASU(ŚRODEK(Datetime_string,12,8)) |
Ciąg daty i godziny: Jest to odwołanie do ciągu zawierającego datę i godzinę.
Główne funkcje w tej ogólnej formule to DATA.WARTOŚĆ oraz WARTOŚĆ CZASU. Inne funkcje są używane do wyodrębniania ciągu daty i godziny, jeśli są połączone w jeden ciąg. I tak, komórka zawierająca tę formułę powinna być sformatowana do formatu daty i godziny, w przeciwnym razie zobaczysz numery seryjne, które mogą Cię zmylić.
Zobaczmy kilka przykładów, aby wszystko było jasne.
Przykład: Konwertuj zaimportowaną datę i godzinę na datę i godzinę w programie Excel
Tutaj mamy datę i godzinę połączone w jeden ciąg. Musimy wyodrębnić datę i godzinę osobno i przekonwertować je na datę i godzinę w programie Excel.
Zwróć uwagę, że sformatowałem C3 jako datę, dzięki czemu wyniki, które otrzymuję, są konwertowane na daty i nie widzę liczby ułamkowej. (Sekrety daty i godziny programu Excel).
Napiszmy więc wzór:
| =DATA.WARTOŚĆ(LEWO(B3,10)) + WARTOŚĆ CZASU(ŚRODEK(B3,12,8)) |
I zwraca datę i godzinę, które program Excel traktuje jako datę i godzinę, a nie ciąg.
Jeśli nie zmienisz formatowania komórek, tak będzie wyglądał Twój wynik.
To nie jest złe. Jest to numer seryjny, który reprezentuje tę datę i godzinę. Jeśli nie wiesz, jak data i godzina są postrzegane przez program Excel, możesz uzyskać tutaj pełną jasność.
Jak to działa?
To proste. Używamy technik ekstrakcji, aby wyodrębnić ciągi daty i godziny. Tutaj mamy LEWY(B3,10). Zwraca to 10 znaków od lewej strony ciągu w B3. Czyli „12.02.2020”. Teraz ten ciąg jest dostarczany do funkcji DATA.WARTOŚĆ. A DATA.WARTOŚĆ konwertuje ciąg daty na jego wartość. Stąd otrzymujemy 44167.
Podobnie otrzymujemy wartość czasu za pomocą funkcji MID (do ekstrakcji) i funkcji CZAS.WARTOŚĆ.
Funkcja MID zwraca 8 znaków z 12. pozycji w ciągu. Czyli „12:23:00”. Ten ciąg jest dostarczany do funkcji CZAS.WARTOŚĆ, która zwraca .51597.
Na koniec dodajemy te liczby. To daje nam numer 44167.51597. Kiedy konwertujemy formatowanie komórek na format MM/DD/RRRR GG:MM AM/PM, otrzymujemy dokładną datę i godzinę.
W powyższym przykładzie data i godzina zostały połączone razem jako ciąg. Jeśli po prostu masz datę jako ciąg bez dodatkowego tekstu lub ciągu, możesz użyć samej funkcji DATA.WARTOŚĆ, aby przekonwertować datę na datę programu Excel.
| =DATA.WARTOŚĆ(B3) |
Jeśli masz tylko czas jako ciąg, użyj WARTOŚĆ CZASUfunkcjonować
| =WARTOŚĆ CZASU(B3) |
Możesz przeczytać o tych funkcjach, klikając nazwę funkcji w formule.
Więc tak, tak można przekonwertować ciąg daty i godziny na wartość daty i godziny w programie Excel. Mam nadzieję, że to było pomocne. Jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu lub masz jakiekolwiek inne zapytania związane z programem Excel/VBA, zapytaj mnie w sekcji komentarzy poniżej. Chętnie Ci pomogę.
Konwertuj datę i godzinę z GMT na CST : Pracując nad raportami Excela i dashboardami Excela w Microsoft Excel, musimy przekonwertować Datę i Czas. Ponadto musimy uzyskać różnicę w taktowaniu w programie Microsoft Excel.
Jak przekonwertować czas na dziesiętny w programie Excel? : Musimy przekonwertować liczby czasu na ułamki dziesiętne za pomocą arkusza kalkulacyjnego w Excel 2016. Możemy manipulować czasem w Excelu 2010, 2013 i 2016 za pomocą funkcji KONWERSJA, GODZINA i MINUTA.
Jak przekonwertować datę na tekst w programie Excel : W tym artykule dowiedzieliśmy się, jak przekonwertować tekst na datę, ale jak przekonwertować datę w programie Excel na tekst. Aby przekonwertować datę w Excelu na tekst, mamy kilka technik.
Konwersja nazwy miesiąca na liczbę w programie Microsoft Excel : Jeśli chodzi o raporty, liczby są lepsze niż tekst. W programie Excel, jeśli chcesz przekonwertować nazwy miesięcy na liczby (1-12). Korzystając z funkcji DATA.WARTOŚĆ i MIESIĄC możesz łatwo przeliczyć miesiące na liczby.
Excel konwertuje sekundy dziesiętne na format czasu : Jak wiemy, czas w Excelu traktowany jest jako liczby. Godziny, minuty i sekundy są traktowane jako liczby dziesiętne. Więc kiedy mamy sekundy jako liczby, jak przekonwertować je na format czasu? Ten artykuł został omówiony.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.