Excel traktuje daty jako liczby całkowite. Oznacza to, że możesz je dodawać i odejmować, co może być przydatne przy określaniu, ile dni pozostało do następnego terminu lub wydarzenia. W tym artykule użyjemy funkcji DATA, ROK, MIESIĄC, DZIEŃ i DZIŚ programu Excel, aby pokazać, jak obliczyć liczbę dni do następnych urodzin lub innych wydarzeń rocznych.
Excel przechowuje daty jako liczby całkowite. Domyślnie Excel używa „1” do reprezentowania 01/01/1900, a każdy następny dzień jest o jeden większy. Wpisz 01/01/2000 i zmień format na „Numer”, a zobaczysz „36526”. Jeśli odejmiesz 1 od 36526, zobaczysz, że w XX wieku było 36525 dni. Alternatywnie możesz wprowadzić przyszłą datę i odjąć wynik funkcji DZIŚ, aby zobaczyć, ile dni pozostało do tej daty od dzisiaj.
Szybkie podsumowanie funkcji związanych z datą
Zanim przejdziemy do kilku przykładów, musimy przyjrzeć się kilku prostym funkcjom związanym z datą, w tym funkcjom Excela DZIŚ, DATA, ROK, MIESIĄC i DZIEŃ.
DZISIAJ
Składnia: = TODAY ()
Wynik: bieżąca data
DATA
Składnia: = DATE (rok, miesiąc, dzień)
Wynik: data określona przez wprowadzony rok, miesiąc i dzień
ROK
Składnia: = YEAR (data)
Wynik: rok z podanej daty
MIESIĄC
Składnia: = MIESIĄC (data)
Wynik: numeryczny miesiąc podanej daty (od 1 do 12)
DZIEŃ
Składnia: = DAY (data)
Wynik: dzień miesiąca z podanej daty
Niektóre przykładowe obliczenia
Przyjrzymy się trzem zdarzeniom, które mają miejsce corocznie tego samego dnia, obliczymy datę ich następnego wystąpienia i określimy liczbę dni między teraz a następnym wystąpieniem.
Oto nasze przykładowe dane. Mamy skonfigurowane cztery kolumny: Event, Date, Next_Occurrence i Days_Until_Next. Wpisaliśmy daty losowej daty urodzenia, termin płatności podatków w USA i Halloween. Daty takie jak urodziny, rocznice i niektóre święta przypadają w określone dni każdego roku i dobrze sprawdzają się w tym przykładzie. Inne święta – takie jak Święto Dziękczynienia – przypadają w określony dzień powszedni w określonym miesiącu; ten przykład nie obejmuje tego typu wydarzeń.
Istnieją dwie możliwości wypełnienia kolumny „Next_Occurrence”. Możesz ręcznie wprowadzić każdą datę, ale każdy wpis będzie musiał zostać ręcznie zaktualizowany w przyszłości w miarę upływu daty. Zamiast tego napiszmy formułę wyrażenia „JEŻELI”, aby program Excel mógł wykonać pracę za Ciebie.
Spójrzmy na urodziny. Znamy już miesiąc = MIESIĄC (F3) i dzień = DZIEŃ (F3) następnego wystąpienia. To łatwe, ale co z rokiem? Potrzebujemy programu Excel, aby wiedzieć, czy urodziny miały już miejsce w tym roku, czy nie. Najpierw musimy obliczyć datę, w której urodziny przypadają w bieżącym roku, używając następującego wzoru:
=DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))
Następnie musimy wiedzieć, czy ta data już minęła i możesz porównać ten wynik z DZIŚ (), aby się tego dowiedzieć. Jeśli jest lipiec, a urodziny przypadają we wrześniu, następne wystąpienie ma miejsce w bieżącym roku, oznaczone jako = ROK (DZISIAJ ()). Jeśli jest grudzień, a urodziny przypadają co roku w maju, to następne wystąpienie jest w następnym roku, więc = ROK (DZISIAJ ()) + 1 daje następny rok. Aby określić, którego użyć, możemy użyć instrukcji „JEŻELI”:
=IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1)
Teraz możemy połączyć wyniki instrukcji JEŻELI z MIESIĄCEM i DNIEM urodzin, aby określić następne wystąpienie. Wprowadź tę formułę do komórki G3:
=DATE(IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(F3),DAY(F3))
= DZIŚ (), ROK (DZIŚ ()), ROK (DZIŚ ()) + 1), MIESIĄC (F3), DZIEŃ (F3)) ”w komórce F3 ′ szerokość =„ 650 ″ wysokość = „250” onload = ” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Naciśnij Enter, aby zobaczyć wynik. (Ten artykuł został napisany pod koniec stycznia 2019 roku, więc daty będą… cóż… przestarzałe.)
Wypełnij tę formułę w komórkach poniżej, podświetlając komórki i naciskając Ctrl + D.
Teraz możemy łatwo określić liczbę dni do następnego wystąpienia, odejmując wynik funkcji TODAY () od wyników Next_Occurrence, które właśnie obliczyliśmy. Wprowadź następującą formułę do komórki H3:
=G3-TODAY()
Naciśnij klawisz Enter, aby zobaczyć wynik, a następnie wypełnij tę formułę w komórkach poniżej, podświetlając komórki i naciskając Ctrl + D.
Możesz zapisać skoroszyt z formułami z tego przykładu, aby śledzić, czyje urodziny zbliżają się w następnej kolejności, lub wiedzieć, ile dni zostało Ci do ukończenia kostiumu na Halloween. Za każdym razem, gdy używasz skoroszytu, ponownie obliczy wyniki na podstawie bieżącej daty, ponieważ została użyta funkcja DZIŚ ().
I tak, są to dość konkretne przykłady, które mogą być dla ciebie przydatne lub nie. Ale służą również do zilustrowania rodzajów rzeczy, które można zrobić za pomocą funkcji związanych z datą w programie Excel.