Formula umum
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Ringkasan
Untuk menghitung hari kerja (Senin, Jumat, Minggu, dll.) Antara dua tanggal, Anda dapat menggunakan rumus array yang menggunakan beberapa fungsi: SUMPRODUCT, WEEKDAY, ROW, dan INDIRECT. Dalam contoh yang diperlihatkan, rumus di sel E6 adalah
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Dalam versi umum rumus, mulai = tanggal mulai, tanggal akhir = akhir, dan dow = hari dalam seminggu.
Penjelasan
Intinya, rumus ini menggunakan fungsi WEEKDAY untuk menguji sejumlah tanggal untuk melihat apakah tanggal tersebut mendarat pada hari tertentu dalam seminggu (dow) dan fungsi SUMPRODUCT untuk menghitung total.
Saat diberi tanggal, WEEKDAY hanya mengembalikan angka antara 1 dan 7 yang sesuai dengan hari tertentu dalam seminggu. Dengan pengaturan default, 1 = Minggu dan 7 = Sabtu. Jadi, 2 = Senin, 6 = Jumat, dan seterusnya.
Trik untuk rumus ini adalah memahami bahwa tanggal di Excel hanyalah nomor seri yang dimulai pada 1 Jan 1900. Misalnya, 1 Januari 2016 adalah nomor seri 42370, dan 8 Januari adalah 42377. Tanggal di Excel hanya terlihat seperti tanggal ketika format nomor tanggal diterapkan.
Jadi, pertanyaannya menjadi - bagaimana Anda dapat membuat array tanggal yang dapat Anda masukkan ke dalam fungsi WEEKDAY untuk mengetahui hari yang sesuai dalam seminggu?
Jawabannya adalah dengan menggunakan ROW dengan fungsi TIDAK LANGSUNG seperti ini:
ROW(INDIRECT(date1&":"&date2))
INDIRECT memungkinkan tanggal gabungan "42370: 42377" diinterpretasikan sebagai nomor baris. Kemudian fungsi ROW mengembalikan array seperti ini:
(42370;42371;42372;42373;42374;42375;42376;42377)
Fungsi WEEKDAY mengevaluasi angka-angka ini sebagai tanggal dan mengembalikan larik ini:
(6;7;1;2;3;4;5;6)
yang diuji terhadap hari tertentu dalam seminggu (6 dalam hal ini, dari D6). Setelah hasil pengujian diubah menjadi 1 dan 0 dengan tanda hubung ganda, array ini diproses oleh SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Yang mengembalikan 2.
Dengan URUTAN
Dengan fungsi SEQUENCE baru, rumus ini dapat disederhanakan seperti ini:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Dalam versi ini, kami menggunakan SEQUENCE untuk menghasilkan larik tanggal secara langsung, tanpa perlu INDIRECT atau ROW.
