Excel przechowuje wartości daty i godziny jako numery seryjne na zapleczu.
Oznacza to, że chociaż możesz zobaczyć datę, taką jak „10 stycznia 2021-2022” lub „01/10/2020” w komórce, na zapleczu Excel przechowuje ją jako liczbę. Jest to przydatne, ponieważ umożliwia użytkownikom łatwe dodawanie/odejmowanie daty i godziny w programie Excel.
W programie Microsoft Excel dla systemu Windows „01 stycznia 1900” jest przechowywany jako 1, „02 stycznia 1900” jest przechowywany jako 2 i tak dalej.
Poniżej obie kolumny mają te same liczby, ale kolumna A pokazuje liczby, a kolumna B pokazuje datę reprezentowaną przez tę liczbę.
Jest to również powód, dla którego czasami możesz spodziewać się daty w komórce, ale w końcu widzisz numer. Widzę to cały czas, gdy pobieram dane z baz danych lub narzędzi internetowych.
Dzieje się tak, gdy format komórki jest ustawiony tak, aby wyświetlać liczbę jako liczbę zamiast daty.
Więc jak możemy? zamień te numery seryjne na daty?
O tym jest ten samouczek.
W tym samouczku pokażę Ci dwa naprawdę proste sposoby konwersji numerów seryjnych na daty w Excelu
Uwaga: Excel dla systemu Windows używa systemu daty 1900 (co oznacza, że 1 będzie reprezentować 01 stycznia 1900, podczas gdy Excel dla komputerów Mac używa systemu daty 1904 (który mężczyzna 1 będzie reprezentował 01 stycznia 1904 w programie Excel na komputerze Mac)Więc zacznijmy!
Konwertuj numery seryjne na daty za pomocą formatowania liczb
Najłatwiejszym sposobem przekonwertowania numeru seryjnego daty na datę jest zmiana formatowania komórek zawierających te liczby.
Niektóre z najczęściej używanych formatów dat można znaleźć na karcie Strona główna na wstążce
Pokażę ci, jak to zrobić.
Korzystanie z opcji wbudowanego formatu daty na wstążce
Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz przekonwertować wszystkie te liczby w kolumnie A na odpowiednią datę, którą reprezentuje.
Poniżej znajdują się kroki, aby to zrobić:
- Wybierz komórki zawierające liczbę, którą chcesz przekonwertować na datę
- Kliknij kartę „Strona główna”
- W grupie „Liczba” kliknij menu rozwijane Formatowanie liczb
- Z menu wybierz opcję „Długa data” lub „Krótka data” (w zależności od tego, w jakim formacie chcesz mieć te liczby)
Otóż to!
Powyższe kroki przekonwertują liczby na wybrany format daty.
Powyższe kroki nie zmieniły wartości w komórce, a jedynie sposób jej wyświetlania.
Zwróć uwagę, że program Excel pobiera krótkie formatowanie daty na podstawie ustawień regionalnych systemu. Na przykład, jeśli jesteś w Stanach Zjednoczonych, format daty to MM/DD/RRRR, a jeśli jesteś w Wielkiej Brytanii, format daty to DD/MM/RRRR.
Chociaż jest to szybka metoda konwersji numerów seryjnych na daty, ma ona kilka ograniczeń:
- Istnieją tylko dwa formaty daty - data krótka i data długa (i to również w określonym formacie). Tak więc, jeśli chcesz uzyskać tylko miesiąc i rok, a nie dzień, nie będziesz w stanie tego zrobić za pomocą tych opcji.
- Nie możesz wyświetlić daty ani godziny, korzystając z opcji formatowania w menu rozwijanym. Możesz wybrać wyświetlanie daty lub godziny, ale nie obu.
Jeśli więc potrzebujesz większej elastyczności w sposobie wyświetlania dat w programie Excel, musisz użyć opcji Niestandardowe formatowanie liczb (omówione w dalszej części tego samouczka).
Czasami, gdy konwertujesz liczbę na datę w Excelu, zamiast daty możesz zobaczyć kilka znaków hash (coś w rodzaju #####). dzieje się tak, gdy szerokość kolumny jest niewystarczająca, aby pomieścić całą datę. W takim przypadku po prostu zwiększ szerokość kolumny.Tworzenie niestandardowego formatu daty za pomocą okna dialogowego Formatowanie liczb
Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz przekonwertować numery seryjne w kolumnie A na daty w następującym formacie - 01.01.2020
Zauważ, że ta opcja nie jest domyślnie dostępna w metodzie wstążki, którą omówiliśmy wcześniej.
Poniżej znajdują się kroki, aby to zrobić:
- Wybierz komórki zawierające liczbę, którą chcesz przekonwertować na datę
- Kliknij kartę „Strona główna”
- W grupie Numbers kliknij ikonę uruchamiania okna dialogowego (jest to mała przechylona strzałka w prawym dolnym rogu grupy)
- W oknie dialogowym „Formatuj komórki”, które się otworzy, upewnij się, że wybrano kartę „Liczba”
- W opcjach „Kategoria” po lewej stronie wybierz „Data”
- Wybierz żądane formatowanie z pola „Typ”.
- Kliknij OK
Powyższe kroki przekonwertują liczby na wybrany format daty.
Jak widać, w tym przypadku jest więcej opcji formatowania daty (w porównaniu z krótką i długą datą, którą otrzymaliśmy ze wstążką).
A jeśli nie znajdziesz formatu, którego szukasz, możesz również utworzyć własny format daty.
Załóżmy na przykład, że chcę wyświetlić datę w następującym formacie - 01.01.2020 (co nie jest jeszcze opcją w oknie dialogowym formatowania komórek).
Oto jak mogę to zrobić:
- Wybierz komórki zawierające liczby, które chcesz przekonwertować na datę
- Kliknij kartę Strona główna
- W grupie Numbers kliknij ikonę uruchamiania okna dialogowego (jest to mała strzałka przechyłu w prawym dolnym rogu grupy)
- W oknie dialogowym „Formatuj komórki”, które się otworzy, upewnij się, że wybrano kartę „Liczba”
- W opcjach „Kategoria” po lewej stronie wybierz „Niestandardowe”
- W polu po prawej wpisz
mm/dd/rrrr
- Kliknij OK
Powyższe kroki zmienią wszystkie liczby na określony format liczb.
Dzięki niestandardowemu formatowi masz pełną kontrolę i pozwala wyświetlać datę w dowolnym formacie. Możesz także utworzyć format, w którym pokazuje datę i godzinę w tej samej komórce.
Na przykład w tym samym przykładzie, jeśli chcesz pokazać datę i godzinę, możesz użyć poniższego formatu:
mm/dd/rrrr gg:mm AM/PM
Poniżej znajduje się tabela przedstawiająca kody formatu daty, których możesz użyć:
| Kod formatowania daty | Jak formatuje datę? |
| m | Pokazuje miesiąc jako liczbę od 1 do 12 |
| mm | Pokaż miesiące jako liczby dwucyfrowe od 01-12 |
| mmm | Pokazuje miesiąc jako trzyliterowy, jak w okresie styczeń-grudzień |
| mmmm | Pokazuje pełną nazwę miesięcy, jak w okresie styczeń-grudzień |
| mmmmm | Pokazuje pierwszy alfabet nazwy miesiąca, jak w J-D |
| D | Pokazuje dni jako 1-31 |
| dd | Pokazuje dni jako 01-31 |
| ddd | Pokazuje dni jako niedz-sob |
| dddd | Pokazuje dni jako niedziela-sobota |
| yy | Pokazuje lata jako 00-99 |
| rrrr | Pokazuje lata jako 1900-9999 |
Konwertuj numery seryjne na daty za pomocą formuły TEKST
Omówione powyżej metody działają poprzez zmianę formatowania komórek, które mają liczby.
Ale w niektórych przypadkach możesz nie być w stanie korzystać z tych metod.
Na przykład poniżej mam zestaw danych, w którym liczby mają wiodący apostrof - który konwertuje te liczby na tekst.
Jeśli spróbujesz użyć wbudowanych opcji do zmiany formatowania komórek z tym zestawem danych, nic by to nie dało. Dzieje się tak, ponieważ Excel nie traktuje ich jako liczb, a ponieważ daty dla liczb, Excel odmawia ich formatowania.
W takim przypadku możesz przekonwertować ten tekst na liczby, a następnie skorzystać z opisanych powyżej metod formatowania lub użyć innej techniki do konwersji liczb seryjnych na daty - za pomocą funkcji TEKST.
Funkcja TEKST przyjmuje dwa argumenty - liczbę, którą należy sformatować, oraz kod formatu.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz przekonwertować wszystkie liczby i kolumnę A na daty.
Poniżej formuła, która mogłaby to zrobić:
=TEKST(A2;"MM/DD/RRRR")
Zauważ, że w formule określiłem formatowanie daty w formacie MM/DD/RRRR. Jeśli musisz użyć innego formatowania, możesz określić jego kod jako drugi argument formuły TEKST.
Możesz również użyć tej formuły, aby wyświetlić datę i godzinę.
Na przykład, jeśli chcesz, aby wynik końcowy był w formacie - 01.01.2020 12:00, użyj poniższej formuły:
=TEKST(A2,"mm/dd/rrrr gg:mm AM/PM")
W powyższym wzorze dodałem również format czasu, więc jeśli w liczbach występują części dziesiętne, zostanie to pokazane jako czas w godzinach i minutach.
Jeśli potrzebujesz tylko daty (a nie podstawowej formuły), przekonwertuj formuły na wartości statyczne, używając funkcji wklejania specjalnego.
Dużą zaletą używania funkcji TEKST do konwertowania liczb seryjnych na daty jest możliwość łączenia wyniku formuły TEKST z innymi formułami. Na przykład możesz połączyć wynikową datę z tekstem i pokazać coś takiego jak - Termin płatności to 01.01.2020 lub Termin upływa 1 stycznia 2020 r.
Uwaga: pamiętaj, że daty i godziny są przechowywane jako liczby, gdzie każda liczba całkowita reprezentuje jeden pełny dzień, a część dziesiętna reprezentuje część dnia. Tak więc 1 oznaczałoby 1 pełny dzień, a 0,5 oznaczałoby pół dnia lub 12 godzin.
Oto dwa proste sposoby konwersji numerów seryjnych na daty w programie Microsoft Excel.
Mam nadzieję, że ten samouczek okazał się przydatny!
