Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara memeriksa apakah suatu tanggal adalah hari libur dan menghitung hari kecuali hari libur di Excel?

Misalnya, Anda memiliki daftar tanggal dan ingin memeriksa apakah ada hari libur nasional dalam daftar, bagaimana Anda bisa menyelesaikannya dengan cepat? Dalam artikel ini saya akan menunjukkan kepada Anda cara menghitung semua tanggal hari libur umum Amerika dalam tahun-tahun tertentu, dan kemudian memeriksa apakah tanggal tersebut adalah hari libur umum Amerika di Excel dengan mudah.


Bagian 1: Hitung hari libur nasional dengan tahun tertentu di Excel

Sebelum memeriksa apakah suatu tanggal adalah hari libur umum, Anda harus mencantumkan semua hari libur dalam tahun-tahun tertentu di Excel. Oleh karena itu, menyiapkan tabel seperti gambar berikut yang ditampilkan akan membuat pekerjaan Anda lebih mudah.

dokter periksa apakah hari libur 1

Ada tiga jenis hari libur umum Amerika:

(1) Jenis pertama adalah hari libur nasional pada tanggal tetap, seperti Tahun Baru pada 1 Januari. kita bisa dengan mudah menghitung hari tahun baru dengan rumus = DATE (Tahun Diberikan, 1,1);

(2) Jenis kedua adalah hari libur umum pada hari kerja tetap, seperti Hari Presiden. Kita bisa dengan mudah menghitung hari presiden dengan rumusnya = TANGGAL (Tahun Diberikan, 1,1) + 14 + PILIH (WEEKDAY (TANGGAL (Tahun Diberikan, 1,1)), 1,0,6,5,4,3,2);

(3) Dan yang terakhir adalah Memorial Day, kita bisa dengan mudah menghitung Memorial Day dengan rumusnya = TANGGAL (Tahun Diberikan, 6,1) -WEEKDAY (TANGGAL (Tahun Diberikan, 6,6)).

Di sini saya membuat daftar rumus untuk menghitung semua hari libur di tabel berikut. Cukup masukkan rumus ke dalam sel yang benar dan tekan Enter kunci satu per satu.

Liburan Sel Rumus
Hari Tahun Baru C2 = TANGGAL (C1,1,1)
Martin Luther King Jr Day C3 = TANGGAL (C1,1,1) + 14 + PILIH (WEEKDAY (TANGGAL (C1,1,1)), 1,0,6,5,4,3,2)
Hari Presiden C4 = TANGGAL (C1,2,1) + 14 + PILIH (WEEKDAY (TANGGAL (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = TANGGAL (C1,6,1) -WEEKDAY (TANGGAL (C1,6,6))
Hari Kemerdekaan C6 = TANGGAL (C1,7,4)
Hari Buruh C7 = TANGGAL (C1,9,1) + PILIH (WEEKDAY (TANGGAL (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = TANGGAL (C1,10,1) + 7 + PILIH (WEEKDAY (TANGGAL (C1,10,1)), 1,0,6,5,4,3,2)
Hari Veteran C9 = TANGGAL (C1,11,11)
Thanksgiving Day C10 = TANGGAL (C1,11,1) + 21 + PILIH (WEEKDAY (TANGGAL (C1,11,1)), 4,3,2,1,0,6,5)
Hari Natal C11 = TANGGAL (C1,12,25)

Catatan: Dalam rumus di tabel di atas, C1 adalah sel referensi yang menempatkan tahun tertentu. Dalam contoh kami, artinya Tahun 2015, dan Anda dapat mengubahnya berdasarkan kebutuhan Anda.

Dengan rumus ini, Anda dapat dengan mudah menghitung tanggal hari libur nasional dengan tahun tertentu. Lihat tangkapan layar di bawah ini:

dokter periksa apakah hari libur 2

Simpan rentang sebagai entri AutoTeks (format sel dan rumus yang tersisa) untuk digunakan kembali di masa mendatang

Pasti sangat membosankan untuk merujuk sel dan menerapkan rumus untuk menghitung setiap hari libur. Kutools for Excel memberikan solusi yang lucu tentang Teks otomatis utilitas untuk menyimpan rentang sebagai entri AutoTeks, yang bisa tetap menjadi format sel dan rumus dalam rentang. Dan kemudian Anda akan menggunakan kembali rentang ini hanya dengan satu klik. Pekerjaan menjadi mudah dengan hanya satu klik untuk menyisipkan tabel ini dan mengubah tahun di tabel ini! Uji Coba Gratis Fitur Lengkap 30 hari!
iklan auto hari libur amerika 1

Kutools untuk Excel - Termasuk lebih dari 300 alat praktis untuk Excel. Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit! Get It Now

Bagian 2: Periksa apakah tanggal adalah hari libur umum di Excel

Setelah mencantumkan semua tanggal hari libur nasional pada tahun-tahun tertentu, kami dapat dengan mudah memeriksa apakah tanggal tersebut adalah hari libur atau tidak dengan rumus di Excel. Misalkan Anda memiliki daftar tanggal seperti yang ditunjukkan screen shot berikut, dan saya akan memperkenalkan cara untuk menyelesaikannya dengan mudah.

Pilih sel kosong di samping daftar tanggal, kata Cell B18, masukkan rumusnya = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") ke dalamnya, lalu seret Fill Handle ke kisaran yang Anda butuhkan. Lihat tangkapan layar di atas:

Catatan:

(1) Dalam rumus = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), $ C $ 2: $ D $ 11 adalah kisaran hari libur nasional pada tahun-tahun tertentu, dan A18 adalah sel dengan tanggal yang ingin Anda periksa apakah ini hari libur umum, dan Anda dapat mengubahnya berdasarkan kebutuhan Anda. Dan rumus ini akan mengembalikan "Libur" jika tanggal spesifiknya adalah hari libur umum, dan mengembalikan "Tidak" jika bukan.

(2) Anda juga dapat menerapkan rumus array ini = JIKA (ATAU ($ C $ 2: $ D $ 11 = A18), "Liburan", "TIDAK") untuk memeriksa apakah tanggal yang sesuai adalah hari libur atau tidak.


Bagian 3: Hitung hari antara dua hari kecuali akhir pekan dan hari libur di Excel

Di Bagian 1, kami telah mencantumkan semua hari libur pada tahun tertentu, dan sekarang metode ini akan memandu Anda untuk menghitung jumlah hari kecuali semua akhir pekan dan hari libur dalam rentang tanggal.

Pilih sel kosong Anda akan mengembalikan jumlah hari, dan masukkan rumus = NETWORKDAYS (E1, E2, B2: B10) ke dalamnya, dan tekan Enter kunci.

Note: Dalam sel di atas, E1 adalah tanggal mulai dari rentang tanggal tertentu, E2 adalah tanggal akhir, dan B2: B10 adalah daftar hari libur yang kami hitung di Bagian 1.

dokter periksa apakah hari libur 6

Sekarang Anda akan mendapatkan jumlah hari kecuali akhir pekan dan hari libur dalam rentang tanggal yang ditentukan.

Salin rumus dengan tepat / statis tanpa mengubah referensi sel di Excel

Kutools untuk Excel Salinan Tepat Utilitas dapat membantu Anda dengan mudah menyalin beberapa rumus persis tanpa mengubah referensi sel di Excel, mencegah referensi sel relatif diperbarui secara otomatis. Uji Coba Gratis Fitur Lengkap 30 hari!
iklan rumus salinan persis 3

Kutools untuk Excel - Termasuk lebih dari 300 alat praktis untuk Excel. Uji coba gratis fitur lengkap 30 hari, tidak perlu kartu kredit! Get It Now


Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (8)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
mencoba membuat rumus untuk mengurangkan HARI KALENDER dan hari libur. Saya telah dapat mengetahui untuk HARI KERJA dan LIBUR, tetapi saya tidak dapat mengetahui bagaimana melakukan hari KALENDER dan hari libur. inilah yang saat ini saya gunakan untuk HARI KERJA DAN LIBUR. Membantu! Jadi saya ingin ini menjadi hari KALENDER, bukan WORKDAYS.] =WORKDAY(B28-5,1,LIBUR)
Komentar ini diminimalkan oleh moderator di situs
Bagaimana saya bisa membuat ini bekerja untuk Hari Libur Federal? Artinya jika tanggal liburan jatuh pada akhir pekan maka hari libur Federal akan menjadi hari Jumat atau Senin.
Komentar ini diminimalkan oleh moderator di situs
Saya menggunakan rumus di atas untuk menghitung hari libur yang sebenarnya dan membuat kolom kedua untuk hari libur yang diamati. Saya membuat rumus ini untuk mencapai ini: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). Referensi sel B15 mengacu pada hari libur yang ada di kolom hari libur sebenarnya, dalam hal ini Hari Tahun Baru. Ketika hari libur yang sebenarnya jatuh pada hari Sabtu, hari libur yang diamati akan terdaftar sebagai hari Jumat dan untuk hari libur yang sebenarnya jatuh pada hari Minggu, hari libur yang diamati akan terdaftar sebagai hari Senin. Semoga ini membantu.
Komentar ini diminimalkan oleh moderator di situs
Ini adalah fungsi akurat yang akan bekerja untuk Hari Tahun Baru yang akan jatuh pada akhir pekan (tahun 2022 dan 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1) ,2)>5))
Komentar ini diminimalkan oleh moderator di situs
Terima kasih, saya telah membuat sistem untuk menentukan apakah hari libur umum adalah hari kerja, tetapi ini juga memberikan alternatif. Masalahnya, jika dilakukan per bulan, maka ada jeda antar hari di mana hari libur terjadi pada hari kerja. Contoh seperti di bawah ini mengambil bagian dari Desember 2017. Angka di sebelah kanan tanggal (Kol B) adalah nilai WEEKDAY. Jika tanggal jatuh pada hari Sabtu atau Minggu (nilai 6 atau 7) maka Kolom C mencerminkan sel kosong ("") jika hari kerja Sel memiliki "1", jika Hari Libur selama hari kerja maka "0" 21 /12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/ 2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 Saya kemudian dapat mengurutkan secara manual menggunakan pendekatan Filter untuk mendapatkan 1 dalam satu kolom baris yang berkesinambungan tanpa kosong atau 0. Salin dan tempel ke lembar kerja tempat saya dapat mengimpor data ke grafik suhu. Saya mencoba untuk membuat bagian filter otomatis baik melalui formula dengan menghapus semua 0 dan sel kosong dengan hasil pergeseran sel yang berisi 1, atau melalui VBA. Hadiah utamanya adalah menggabungkan langkah-langkah di Kolom A dan Kolom C menjadi satu formula. Permainan akhirnya adalah mengisi grafik suhu dengan nama hari kerja dan di baris berikutnya yang sesuai hari bulan yang diperlukan Sen Sel Kam Jum 7 8 10 11 Menggunakan Agustus sebagai contoh di mana tanggal 9 adalah hari libur umum yang jatuh selama bekerja hari, mengakibatkan data yang berkaitan dengan Rabu dihapus dan kolom lainnya bergeser ke atas satu (atau lebih) tempat. Kemudian ditransposisikan ke sel-sel di atas. Saya harap saya menjelaskan dengan cukup jelas :-)
Komentar ini diminimalkan oleh moderator di situs
Bagaimana membuatnya menunjukkan nama liburan yang sebenarnya, bukan hanya "liburan"?
Komentar ini diminimalkan oleh moderator di situs
Hai KC,
Anda dapat mengubah teks “liburan” menjadi INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) dalam rumus, dan seluruh rumus akan diubah menjadi
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Harap dicatat bahwa tanggal yang akan Anda periksa harus ditempatkan dalam satu kolom.
Komentar ini diminimalkan oleh moderator di situs
Bagaimana Anda menghitung 15 hari kalender dari tanggal tertentu termasuk akhir pekan tetapi tidak termasuk hari libur menggunakan daftar/tabel tanggal liburan?
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan