Czyszczenie danych zawierających wartości dat

Dane, które uzyskujesz z różnych źródeł, mogą zawierać wartości dat. W tym rozdziale dowiesz się, jak przygotować dane zawierające wartości danych do analizy.

Dowiesz się o -

  • Formaty dat
    • Data w formacie seryjnym
    • Data w różnych formatach miesiąc-dzień-rok
  • Konwersja dat w formacie seryjnym na format miesiąc-dzień-rok
  • Konwersja dat w formacie miesiąc-dzień-rok na format seryjny
  • Uzyskiwanie dzisiejszej daty
  • Znajdowanie dnia roboczego po określonych dniach
  • Dostosowywanie definicji weekendu
  • Liczba dni roboczych między dwoma podanymi datami
  • Wyodrębnianie roku, miesiąca, dnia z daty
  • Wyodrębnianie dnia tygodnia z daty
  • Uzyskanie daty z roku, miesiąca i dnia
  • Obliczanie liczby lat, miesięcy i dni między dwiema datami

Formaty dat

Excel obsługuje Date wartości na dwa sposoby -

  • Format seryjny
  • W różnych formatach rok-miesiąc-dzień

Możesz konwertować -

  • ZA Date w formacie seryjnym do Date w formacie rok-miesiąc-dzień

  • ZA Date w formacie rok-miesiąc-dzień do pliku Date w formacie seryjnym

Data w formacie seryjnym

ZA Date w formacie szeregowym to dodatnia liczba całkowita reprezentująca liczbę dni między podaną datą a 1 stycznia 1900 r. Zarówno bieżąca Datei 1 stycznia 1900 są wliczane do liczby. Na przykład 42354 to plikDate co oznacza 12/16/2015.

Data w formatach miesiąc-dzień-rok

Excel obsługuje różne pliki Date Formaty oparte na Locale(Lokalizacja), którą wybierzesz. Dlatego musisz najpierw określić zgodność swojegoDateformatów i analizy danych. Zauważ, że pewneDate formaty są poprzedzone * (gwiazdką) -

  • Date formaty zaczynające się od * (gwiazdka) reagują na zmiany regionalnych ustawień daty i godziny określonych dla systemu operacyjnego

  • Date formaty bez * (gwiazdka) nie mają wpływu na ustawienia systemu operacyjnego

Dla zrozumienia można przyjąć Stany Zjednoczone jako ustawienia regionalne. Znajdziesz następująceDate formaty do wyboru dla Date- 8 th czerwca 2016 -

  • * 6/8/2016 (dotyczy ustawień systemu operacyjnego)
  • * Środa, 8 czerwca 2016 r. (Dotyczy ustawień systemu operacyjnego)
  • 6/8
  • 6/8/16
  • 06/08/16
  • 8-Jun
  • 8-Jun-16
  • 08-Jun-16
  • Jun-16
  • June-16
  • J
  • J-16
  • 6/8/2016
  • 8-Jun-2016

Jeśli wpiszesz tylko dwie cyfry reprezentujące rok i jeśli -

  • Cyfry to 30 lub więcej, program Excel zakłada, że ​​cyfry oznaczają lata dwudziestego wieku.

  • Cyfry są mniejsze niż 30, program Excel zakłada, że ​​cyfry oznaczają lata w XXI wieku.

Na przykład 1/1/29 jest traktowany jako 1 stycznia 2029 r., A 1/1/30 jest traktowany jako 1 stycznia 1930 r.

Konwersja dat w formacie seryjnym na format miesiąc-dzień-rok

Aby przekonwertować daty z formatu seryjnego na format miesiąc-dzień-rok, wykonaj czynności podane poniżej -

  • Kliknij Number kartę w Format Cells Okno dialogowe.

  • Kliknij Date pod Category.

  • Wybierz Locale. DostępneDate formaty zostaną wyświetlone jako lista pod Type.

  • Kliknij plik Format pod Type aby spojrzeć na podgląd w polu obok Sample.

Po wybraniu formatu kliknij OK.

Konwersja dat w formacie miesiąc-dzień-rok na format seryjny

Możesz konwertować daty w formacie miesiąc-dzień-rok na format seryjny na dwa sposoby -

  • Za pomocą Format Cells Okno dialogowe

  • Korzystanie z programu Excel DATEVALUE funkcjonować

Korzystanie z okna dialogowego Formatowanie komórek

  • Kliknij Number kartę w Format Cells Okno dialogowe.

  • Kliknij General pod Category.

Korzystanie z funkcji DATEVALUE programu Excel

Możesz użyć programu Excel DATEVALUE funkcja do konwersji Date do Serial Numberformat. Musisz załączyć plikDateargument w „”. Na przykład,

= DATEVALUE („6/8/2016”) daje 42529

Uzyskiwanie dzisiejszej daty

Jeśli chcesz wykonać obliczenia na podstawie dzisiejszej daty, po prostu użyj funkcji programu Excel DZISIAJ (). Wynik odzwierciedla datę użycia.

Poniższy zrzut ekranu Dzisiaj () Wykorzystanie funkcji została podjęta w dniu 16 th maja 2016 -

Znajdowanie dnia roboczego po określonych dniach

Konieczne może być wykonanie pewnych obliczeń na podstawie dni roboczych.

Dni robocze nie obejmują dni weekendowych i świąt. Oznacza to, że jeśli możesz zdefiniować swój weekend i święta, wszelkie obliczenia, które wykonasz, będą oparte na dniach roboczych. Na przykład możesz obliczyć termin płatności faktur, przewidywany czas dostawy, datę następnego spotkania itp.

Możesz użyć programu Excel WORKDAY i WORKDAY.INTL funkcje do takich operacji.

S.No. Opis funkcji
1.

WORKDAY

Zwraca liczbę kolejną daty przed określoną liczbą dni roboczych lub po niej

2.

WORKDAY.INTL

Zwraca liczbę kolejną daty przed lub po określonej liczbie dni roboczych za pomocą parametrów wskazujących, które i ile dni są dniami weekendowymi

Na przykład, można określić 15 th dzień roboczy od dzisiaj (zrzut ekranu poniżej zostanie podjęta w dniu 16 th maja 2016) za pomocą funkcji dziś i roboczym.

Załóżmy, 25 th maja 2016 i 1 st czerwca 2016 są święta. Wtedy twoje obliczenia będą następujące -

Dostosowywanie definicji weekendu

Domyślnie weekend to sobota i niedziela, czyli dwa dni. Możesz również opcjonalnie zdefiniować swój weekend za pomocąWORKDAY.INTLfunkcjonować. Możesz określić swój własny weekend za pomocą numeru weekendu, który odpowiada dniom weekendu, jak podano w poniższej tabeli. Nie musisz zapamiętywać tych liczb, ponieważ gdy zaczniesz wpisywać funkcję, otrzymasz listę liczb i dni weekendowych na liście rozwijanej.

Weekendowe dni Numer weekendu
Sobota niedziela 1 lub pominięty
Niedziela poniedziałek 2
Poniedziałek wtorek 3
Wtorek środa 4
Środa Czwartek 5
Czwartek piątek 6
Piątek sobota 7
Tylko niedziela 11
Tylko w poniedziałek 12
Tylko we wtorek 13
Tylko w środę 14
Tylko w czwartek 15
Tylko w piątek 16
Tylko w sobotę 17

Załóżmy, że jeśli weekend przypada tylko w piątek, musisz użyć liczby 16 w funkcji DZIEŃ.ROBOCZY.INTL.

Liczba dni roboczych między dwoma podanymi datami

Może zaistnieć potrzeba obliczenia liczby dni roboczych między dwiema datami, na przykład w przypadku obliczania płatności na rzecz pracownika kontraktowego, który otrzymuje wynagrodzenie za dzień.

Liczbę dni roboczych między dwiema datami można znaleźć za pomocą funkcji programu Excel NETWORKDAYS i NETWORKDAYS.INTL. Podobnie jak w przypadku DNI.ROBOCZY i DNI.ROBOCZE.INTL, DNI.ROBOCZE i DNI.ROBOCZE.INTL umożliwiają określenie dni wolnych, aw przypadku DNI.ROBOCZY.INTL można dodatkowo określić weekend.

S.No. Opis funkcji
1.

NETWORKDAYS

Zwraca liczbę pełnych dni roboczych między dwiema datami

2.

NETWORKDAYS.INTL

Zwraca liczbę pełnych dni roboczych między dwiema datami przy użyciu parametrów wskazujących, które i ile dni przypadają na weekendy

Możesz obliczyć liczbę dni roboczych między dniem dzisiejszym a inną datą za pomocą funkcji DZIŚ i DNI.ROBOCZE. W podanym poniżej zrzut ekranu, dzisiaj jest 16 th maja 2016, a data zakończenia jest 16 th czerwca 2016. 25 th maja 2016 i 1 st czerwca 2016 są święta.

Ponownie zakłada się, że weekend przypada na sobotę i niedzielę. Możesz mieć własną definicję weekendu i obliczyć liczbę dni roboczych między dwiema datami za pomocą funkcji NETWORKDAYS.INTL. Na poniższym zrzucie ekranu tylko piątek jest zdefiniowany jako weekend.

Wyodrębnianie roku, miesiąca, dnia z daty

Możesz wyodrębnić z każdej daty na liście dat, odpowiadający jej dzień, miesiąc i rok, korzystając z funkcji programu Excel DZIEŃ, MIESIĄC i ROK.

Weźmy na przykład pod uwagę następujące daty -

Z każdej z tych dat można wyodrębnić dzień, miesiąc i rok w następujący sposób -

Wyodrębnianie dnia tygodnia z daty

Możesz wyodrębnić z każdej daty na liście dat, odpowiadający jej dzień tygodnia za pomocą funkcji WEEKDAY programu Excel.

Rozważmy ten sam przykład podany powyżej.

Uzyskanie daty z roku, miesiąca i dnia

Twoje dane mogą mieć osobno informacje o roku, miesiącu i dniu. Musisz uzyskać datę łączącą te trzy wartości, aby wykonać jakiekolwiek obliczenia. Aby uzyskać wartości dat, możesz użyć funkcji DATA.

Weź pod uwagę następujące dane -

Użyj funkcji DATA, aby uzyskać wartości DATA.

Obliczanie lat, miesięcy i dni między dwiema datami

Być może trzeba będzie obliczyć czas, który upłynął od określonej daty. Możesz potrzebować tych informacji w postaci lat, miesięcy i dni. Prostym przykładem byłoby obliczenie obecnego wieku osoby. To faktycznie różnica między datą urodzenia a dniem dzisiejszym. W tym celu możesz użyć funkcji Excel DATEDIF, TODAY i CONCATENATE.

Dane wyjściowe są następujące -