Loncat ke daftar isi utama

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!

Dapatkan uji coba gratis fitur lengkap selama 30 hari sekarang!

iklan auto hari libur amerika 1

Kutools untuk Excel - Tingkatkan Excel dengan lebih dari 300 alat penting. Nikmati uji coba GRATIS 30 hari berfitur lengkap tanpa memerlukan 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.

Dapatkan uji coba gratis fitur lengkap selama 30 hari sekarang!

iklan rumus salinan persis 3

Kutools untuk Excel - Tingkatkan Excel dengan lebih dari 300 alat penting. Nikmati uji coba GRATIS 30 hari berfitur lengkap tanpa memerlukan kartu kredit! Get It Now

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

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.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (9)
No ratings yet. Be the first to rate!
Tony
Tony
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
Reply
0
0
BethBZ
BethBZ
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
Reply
0
0
KC
KC
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
Reply
0
0
kellytte
kellytte     KC
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
Reply
Report
0
0
Shawn Bull
Shawn Bull
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "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 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
Reply
0
0
Mark
Mark
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
Reply
0
0
Tboo Ted Marshall
Tboo Ted Marshall     Mark
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
Reply
0
0
Josh
Josh     Tboo Ted Marshall
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
Reply
0
0
LINDA
LINDA
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations