Data i czas w Excelu są traktowane w Excelu nieco inaczej niż w innych programach do obsługi arkuszy kalkulacyjnych. Jeśli nie wiesz, jak działa data i godzina programu Excel, możesz napotkać niepotrzebne błędy.
W tym artykule dowiemy się wszystkiego o dacie i godzinie Excela. Dowiemy się, co to są daty w Excelu, jak dodać czas w Excelu, jak formatować datę i godzinę w Excelu, czym są funkcje daty i czasu w Excelu, jak wykonywać obliczenia daty i czasu (dodawanie, odejmowanie, mnożenie itp. z datami i godzinami).
Co to jest data i godzina w programie Excel?
Wielu z was może już wiedzieć, że daty i godziny w programie Excel to tylko numery seryjne. Data jest liczbą całkowitą, a godzina jest liczbą ułamkową. Daty w Excelu mają różne formatowanie regionalne. Na przykład w moim systemie jest to mm/dd/RRRR (będziemy używać tego formatu w całym artykule). Możesz używać tego samego formatu daty lub możesz używać formatu daty dd/mm/RRRR.
Formatowanie daty w komórce
Dostępnych jest wiele opcji formatowania daty w programie Excel. Wybierz komórkę, która może zawierać datę i naciśnij klawisze CTRL+1. Spowoduje to otwarcie okna dialogowego Formatowanie komórek. Tutaj możesz zobaczyć dwie opcje formatowania jako Data i Czas. W tych kategoriach dostępnych jest wiele formatów dat, które odpowiadają Twoim wymaganiom.
Daktyle:
Daty w programie Excel to numery seryjne klaczy zaczynające się od 1 stycznia-1900. Dzień w programie Excel to 1. Stąd 1 stycznia-1900 to 1, 2 stycznia-1900 to 2, a 1 stycznia 2000 to 36526.
Ciekawostka: 1900 nie był rokiem przestępnym, ale Excel przyjmuje 29 lutego 1900 jako prawidłową datę. W tamtych czasach rywalizacja w Lotusie 1-2-3 była desperacką usterką.Skrót do wprowadzenia statycznej dzisiejszej daty w programie Excel to CTRL+; (Średnik).
Aby dodać lub odjąć dzień od daty, wystarczy odjąć lub dodać tę liczbę dni do tej daty.
Czas:
Excel domyślnie stosuje format hh:mm dla czasu (format od 0 do 23). Godziny i minuty są oddzielone dwukropkiem bez spacji. Możesz go zmienić na format gg:mm AM/PM. AM/PM musi mieć 1 spację od wartości czasu. Aby uwzględnić sekundy, możesz dodać :ss do gg:mm (gg:mm:ss). Każdy inny format czasu jest nieprawidłowy.
Czas zawsze kojarzy się z datą. Data poprzedza wartość czasu oddzieloną spacją od czasu. Jeśli nie wspomnisz o dacie poprzedzającej godzinę, domyślnie przyjmuje się pierwszą datę programu Excel (czyli 1.01.1900). Czas w programie Excel to liczba ułamkowa. Jest pokazany po prawej stronie przecinka.
Godziny:
Ponieważ 1 dzień to 1 w Excelu, a 1 dzień to 24 godziny, 1 godzina to 1/24 w Excelu. Co to znaczy? Oznacza to, że jeśli chcesz dodać lub odjąć 1 godzinę do czasu, musisz dodać lub odjąć 1/24. Zobacz obrazek poniżej.
można powiedzieć, że 1 godzina równa się 0,041666667 (1/24).
Oblicz godziny między czasem w Excelu
Minuty:
Z wyjaśnienia godziny w Excelu musiałeś zgadnąć, że 1 minuta w Excelu jest równa 1/(24x60) (lub 1/1440 lub0.000694444).
Jeśli chcesz dodać minutę do czasu Excela, dodaj 1/(24x60).Zobacz obrazek poniżej. Czasami masz potrzebę obliczenia minut między datami i godzinami W programie Excel możesz to przeczytać tutaj.
Sekundy:
Tak, sekunda w Excelu jest równa1/(24x60x60).Aby dodać lub odjąć sekundy od czasu, wystarczy zrobić to samo, co zrobiliśmy w ciągu minut i godzin.
Data i godzina w jednej komórce
Daty i godziny są ze sobą powiązane. Data jest zawsze powiązana z prawidłową datą, a godzina jest zawsze powiązana z prawidłową datą programu Excel. Nawet jeśli nie jesteś w stanie zobaczyć jednego z nich.
Jeśli w komórce wprowadzisz tylko godzinę, data w komórce to 1 stycznia 1900, nawet jeśli nie możesz jej zobaczyć. Jeśli sformatujesz tę komórkę jako format daty i godziny, zobaczysz powiązaną datę. Podobnie, jeśli nie wspomnisz o godzinie z datą, domyślnie dołączana jest godzina 12:00. Zobacz obrazek poniżej.
Na powyższym obrazku mamy czas tylko w B3 i datę tylko w B4. Kiedy sformatujemy te komórki jako mm/dd/rr gg:mm, otrzymamy zarówno godzinę, jak i datę w obu komórkach.
Tak więc, wykonując obliczenia daty i godziny w programie Excel, miej to pod kontrolą.
Brak czasu ujemnego
Jak już wspomniałem data i godzina w Excelu zaczyna się od 1 stycznia-1900 o 12:00. Wcześniej nie jest to prawidłowa data w programie Excel. Jeśli odejmiesz wartość od daty poprzedzającej 1 stycznia-1900 12:00, nawet jedną sekundę, program Excel wygeneruje błąd ######. Mówiłem o tym tutaj oraz w Konwertuj datę i godzinę z GMT na CST. Dzieje się tak, gdy próbujemy odjąć coś, co prowadzi do przed 1 stycznia-1900 12:00. Spróbuj sam. Napisz 12:00 i odejmij od tego 13 godzin. zobacz, co dostaniesz.
Obliczenia z datami i godzinami w Excelu
Dodawanie Dni do daty:
Dodawanie dni do daty w Excelu jest łatwe. Aby dodać dzień do daty, wystarczy dodać do niego 1. Zobacz obrazek poniżej.
Nie należy dodawać dwóch dat, aby uzyskać przyszłą datę, ponieważ będzie to sumować numery seryjne tych dni i możesz otrzymać datę w odległej przyszłości.
Odejmowanie dni od daty:
Jeśli chcesz uzyskać datę wsteczną od daty sprzed kilku dni, po prostu odejmij tę liczbę dni od daty, a otrzymasz datę wsteczną. Na przykład, jeśli chcę wiedzieć, która data była przed 56 dniami od DZISIAJ, zapisałbym tę formułę w komórce.
| =DZIŚ()-56 |
To zwróci nam datę 56 przed bieżącą datą.
Uwaga: Pamiętaj, że nie możesz mieć daty przed 1 stycznia/1900 w Excelu. Jeśli pojawi się błąd ######, może to być przyczyną.
Dni między dwiema datami:
Aby obliczyć dni między dwiema datami, wystarczy odjąć datę początkową od daty końcowej. Zrobiłem już artykuł na ten temat. Idź i sprawdź to tutaj. Możesz również użyć funkcji DNI programu Excel, aby obliczyć dni między datą początkową a datą końcową.
Dodawanie razy:
Pojawiło się wiele pytań o to, jak wydłużyć czas, aby osiągnąć sukces, ponieważ wiele osób uzyskuje mylące wyniki, gdy to robią. Czasami istnieją dwa rodzaje dodawania. Jeden dodaje czas do innego czasu. W takim przypadku oba czasy są sformatowane w formacie godziny gg:mm. W takim przypadku możesz po prostu dodać te czasy.
Drugi przypadek dotyczy sytuacji, gdy nie masz dodatkowego czasu w formacie czasu. Masz tylko do dodania liczbę godzin, minut i sekund. W takim przypadku musisz przekonwertować te liczby na ich ekwiwalenty czasowe. Zwróć uwagę na te punkty, aby dodać godziny, minuty i sekundy do daty/czasu.
- Aby dodać N godzin do czasu X, użyj formuły =X+(N/24) (Jako 1=24 godziny)
- Aby dodać N minut do czasu X, użyj wzoru = X+(N/(24*60))
- Aby dodać N sekund do czasu X, użyj wzoru = X+(N/(24*60*60))
Odejmowanie czasów
To to samo, co dodawanie czasu, tylko upewnij się, że podczas odejmowania nie otrzymasz ujemnej wartości czasu, ponieważ w programie Excel nie ma czegoś takiego jak liczba ujemna.
Uwaga: Po dodaniu lub odjęciu czasu w programie Excel, który przekracza 24 godziny różnicy, program Excel przejdzie do następnej lub poprzedniej daty. Na przykład, jeśli odejmiesz 2 godziny od godziny 1:00 w dniu 29 stycznia 2019 r., nastąpi powrót do godziny 23:00 w dniu 28 stycznia 2019 r. Jeśli odejmiesz 2 godziny od 1:00 (nie ma podanej daty), Excel zwróci błąd ######. Powód podałem na początku artykułu.Dodawanie miesięcy do daty:Nie możesz po prostu dodać wielokrotności 30, aby dodać miesiące do daty, ponieważ różne miesiące mają różną liczbę dni. Musisz być ostrożny podczas dodawania miesięcy do Date. Aby dodać miesiące do daty w programie Excel, używamy funkcji EDATE programu Excel. Tutaj mam osobny artykuł na temat dodawania miesięcy do daty w różnych scenariuszach.
Dodawanie lat do daty:
Podobnie jak dodawanie miesięcy do daty, dodanie lat do daty nie jest proste. Aby dodać lata do daty, musimy użyć funkcji ROK, MIESIĄC, DZIEŃ. O dodawaniu lat do tej pory możesz przeczytać tutaj.
Jeśli chcesz obliczyć lata między datami, możesz tego użyć.
Funkcje obsługi daty i czasu w programie Excel:
Ponieważ data i godzina w programie Excel są wyjątkowe, program Excel udostępnia specjalne funkcje do ich obsługi. Tutaj wymieniam kilka z nich.
- DZIŚ(): Ta funkcja dynamicznie zwraca dzisiejszą datę.
- DAY(): Zwraca dzień miesiąca (zwraca liczbę od 1 do 31).
- DAYS(): Służy do liczenia dni między dwiema datami.
- MONTH(): Używany do pobrania miesiąca daty (zwraca liczbę od 1 do 12).
- YEAR(): Zwraca rok z daty.
- WEEKNUM(): Zwraca tygodniowy numer daty w roku.
- WEEKDAY(): Zwraca numer dnia w tygodniu (1 do 7) podanej daty.
- WORKDAY(): Służy do obliczania dni roboczych.
- TIMEVALUE(): Służy do wyodrębniania wartości czasu (numeru seryjnego) z daty i czasu w formacie tekstowym.
- DATEVALUE(): Służy do wyodrębniania wartości daty (numeru seryjnego) z daty i czasu w formacie tekstowym.
Oto niektóre z najbardziej przydatnych funkcji danych i czasu w programie Excel. Istnieje wiele innych funkcji daty i czasu. Możesz je sprawdzić tutaj.
Obliczenia daty i czasu
Jeśli wyjaśnię je wszystkie tutaj, ten artykuł będzie zbyt długi. Te techniki obliczania czasu w Excelu podzieliłem na osobne artykuły. Tutaj o nich wspominam. Możesz je kliknąć, aby przeczytać.
- Oblicz dni, miesiące i lata
- Oblicz wiek od daty urodzenia
- Mnożenie wartości czasu i liczb.
- Uzyskaj nazwę miesiąca z daty w programie Excel
- Pobierz nazwę dnia z daty w programie Excel
- Jak uzyskać ćwierć roku od daty
- Jak dodać dni robocze w programie Excel
- Wstaw znacznik daty i czasu za pomocą VBA
Więc tak, chodzi o datę i godzinę w programie Excel, o których musisz wiedzieć. Mam nadzieję, że ten artykuł był dla Ciebie przydatny. Jeśli masz jakieś pytania lub sugestie, zapisz je w sekcji komentarzy poniżej.