Jak sprawdzić, czy data jest dniem ustawowo wolnym od pracy i liczyć dni oprócz dni wolnych w programie Excel?
Na przykład masz listę dat i chcesz sprawdzić, czy na liście znajdują się święta państwowe, jak możesz to szybko zrobić? W tym artykule pokażę sposoby obliczania wszystkich dat amerykańskich świąt państwowych w danych latach, a następnie łatwo sprawdzę, czy data jest amerykańskim świętem państwowym w Excelu.
- Część 1: Oblicz dni świąteczne w danych latach w programie Excel
- Część 2: Sprawdź, czy data jest dniem ustawowo wolnym od pracy w programie Excel
- Część 3: Policz dni między dwoma dniami z wyjątkiem weekendów i świąt w programie Excel
Część 1: Oblicz dni świąteczne z podanym rokiem w programie Excel
Przed sprawdzeniem, czy data jest dniem ustawowo wolnym od pracy, należy wypisać wszystkie dni wolne w danych latach w programie Excel. Dlatego przygotowanie tabeli, jak pokazano na poniższym zrzucie ekranu, ułatwi pracę.
Istnieją trzy rodzaje amerykańskich świąt państwowych:
(1) Pierwszy rodzaj to święto państwowe w ustalonym dniu, np. Nowy Rok przypada 1 stycznia. możemy łatwo obliczyć Nowy Rok za pomocą wzoru = DATA (podany rok, 1,1);
(2) Drugi rodzaj to święto państwowe w ustalony dzień powszedni, na przykład Dzień Prezydenta. Możemy łatwo obliczyć dzień Prezydenta za pomocą wzoru = DATA (dany rok, 1,1) + 14 + WYBIERZ (WEEKDAY (DATA (dany rok, 1,1)), 1,0,6,5,4,3,2);
(3) A ostatnim rodzajem jest Dzień Pamięci, możemy łatwo obliczyć Dzień Pamięci za pomocą wzoru = DATA (dany rok, 6,1) -WEEKDAY (DATA (dany rok, 6,6)).
Tutaj podaję formuły do obliczania wszystkich dni ustawowo wolnych od pracy w poniższej tabeli. Po prostu wprowadź formuły do odpowiedniej komórki i naciśnij Wchodzę klucz jeden po drugim.
| Wakacje | Komórka | Wzory |
| Nowy Rok | C2 | = DATA (C1,1,1; XNUMX; XNUMX) |
| Dzień Martina Luthera Kinga Jr. | C3 | = DATA (C1,1,1) + 14 + WYBIERZ (WEEKDAY (DATA (C1,1,1)), 1,0,6,5,4,3,2) |
| Dzień Prezydenta | C4 | = DATA (C1,2,1) + 14 + WYBIERZ (WEEKDAY (DATA (C1,2,1)), 1,0,6,5,4,3,2) |
| Dzień pamięci | C5 | = DATA (C1,6,1) -WEEKDAY (DATE (C1,6,6)) |
| Dzień Niepodległości | C6 | = DATA (C1,7,4; XNUMX; XNUMX) |
| Labor Day | C7 | = DATA (C1,9,1) + WYBIERZ (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2) |
| Columbus Day | C8 | = DATA (C1,10,1) + 7 + WYBIERZ (WEEKDAY (DATA (C1,10,1)), 1,0,6,5,4,3,2) |
| Dzień Weteranów | C9 | = DATA (C1,11,11; XNUMX; XNUMX) |
| Dzień Dziękczynienia | C10 | = DATA (C1,11,1) + 21 + WYBIERZ (WEEKDAY (DATA (C1,11,1)), 4,3,2,1,0,6,5) |
| Boże Narodzenie | C11 | = DATA (C1,12,25; XNUMX; XNUMX) |
Uwaga: We wzorach w powyższej tabeli C1 jest komórką odniesienia, która lokalizuje dany rok. W naszym przykładzie oznacza to rok 2015 i możesz go zmienić w zależności od potrzeb.
Dzięki tym formułom można łatwo obliczyć daty świąt państwowych w podanych latach. Zobacz zrzut ekranu poniżej:
Zapisz zakres jako wpis Autotekstu (pozostałe formaty komórek i formuły) do ponownego wykorzystania w przyszłości
Odwoływanie się do komórek i stosowanie formuł do obliczania każdego święta musi być bardzo żmudne. Kutools dla programu Excel zapewnia urocze obejście Automatyczny tekst narzędzie do zapisywania zakresu jako wpisu Autotekstu, który może pozostać formatami komórek i formułami w zakresie. Następnie jednym kliknięciem ponownie użyjesz tego zakresu. Praca staje się łatwa, wystarczy jedno kliknięcie, aby wstawić tę tabelę i zmienić rok w tej tabeli!
Kutools dla programu Excel - Doładuj program Excel za pomocą ponad 300 niezbędnych narzędzi. Ciesz się w pełni funkcjonalnym 30-dniowym DARMOWYM okresem próbnym bez karty kredytowej! Zdobądź To Teraz
Część 2: Sprawdź, czy data jest dniem ustawowo wolnym od pracy w programie Excel
Po wypisaniu wszystkich dat dni ustawowo wolnych od pracy w określonych latach możemy łatwo sprawdzić, czy data jest dniem ustawowo wolnym od pracy, czy nie, za pomocą formuł w Excelu. Przypuśćmy, że masz listę dat, jak pokazano na poniższym zrzucie ekranu, a ja przedstawię sposoby na łatwe wykonanie tego.
Wybierz pustą komórkę obok listy dat, mówi Komórka B18, wprowadź formułę = JEŻELI (LICZ.JEŻELI (2 C $: 11 $ D, A18), "Wakacje", "Nie") do niego, a następnie przeciągnij uchwyt wypełnienia do wymaganego zakresu. Zobacz zrzut ekranu powyżej:
Uwagi:
(1) W formule = JEŻELI (LICZ.JEŻELI (2 C $: 11 $ D, A18), "Święto", "Nie"), 2 C $: 11 $ D to zakres świąt państwowych w określonych latach, a A18 to komórka z datą, którą chcesz sprawdzić, czy jest to święto państwowe, i możesz je zmienić w zależności od potrzeb. Ta formuła zwróci „Święto”, jeśli określona data jest dniem ustawowo wolnym od pracy, i zwróci „Nie”, jeśli nie jest.
(2) Możesz również zastosować tę formułę tablicową = JEŻELI (LUB (2 C $: 11 $ D 18 = AXNUMX), "Wakacje", "NIE") aby sprawdzić, czy odpowiednia data jest dniem wolnym od pracy, czy nie.
Część 3: Policz dni między dwoma dniami z wyjątkiem weekendów i świąt w programie Excel
W części 1 wymieniliśmy wszystkie święta w danym roku, a teraz ta metoda pomoże Ci policzyć liczbę dni z wyjątkiem wszystkich weekendów i świąt w zakresie dat.
Wybierz pustą komórkę, w której zwrócisz liczbę dni, i wprowadź formułę = NETWORKDAYS (E1, E2, B2: B10) do niego i naciśnij Wchodzę klawisz.
Note: W powyższych komórkach E1 to data rozpoczęcia określonego zakresu dat, E2 to data końcowa, a B2: B10 to lista dni wolnych, którą obliczamy w części 1.
Teraz otrzymasz liczbę dni oprócz weekendów i świąt w określonym zakresie dat.
Kopiuj formuły dokładnie / statycznie bez zmiany odwołań do komórek w programie Excel
Kutools dla programu Excel Dokładna kopia Narzędzie może pomóc w łatwym kopiowaniu wielu formuł dokładnie bez zmiany odwołań do komórek w programie Excel, zapobiegając automatycznej aktualizacji względnych odwołań do komórek.
Kutools dla programu Excel - Doładuj program Excel za pomocą ponad 300 niezbędnych narzędzi. Ciesz się w pełni funkcjonalnym 30-dniowym DARMOWYM okresem próbnym bez karty kredytowej! Zdobądź To Teraz
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
