Formula umum
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Ringkasan
Untuk menghitung jam kerja antara dua tanggal dengan jadwal kustom, Anda bisa menggunakan rumus berdasarkan fungsi WEEKDAY dan SUMPRODUCT, dengan bantuan dari ROW, INDIRECT, dan MID. Dalam contoh yang ditampilkan, rumus di F8 adalah:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Yang mengembalikan 36 jam, berdasarkan jadwal khusus di mana 8 jam bekerja Sen-Jum, 4 jam bekerja pada hari Sabtu, dan Senin 3 September adalah hari libur. Liburan disediakan sebagai rentang bernama G6: G8. Jadwal kerja dimasukkan sebagai string teks di kolom D dan dapat diubah sesuai keinginan.
Catatan: Ini adalah rumus array yang harus dimasukkan dengan Control + Shift + Enter. Jika Anda memiliki hari kerja standar 8 jam, rumus ini lebih sederhana.
Penjelasan
Pada intinya, rumus ini menggunakan fungsi WEEKDAY untuk mengetahui hari dalam seminggu (yaitu Senin, Selasa, dll.) Untuk setiap hari di antara dua tanggal yang ditentukan. WEEKDAY mengembalikan angka antara 1 dan 7. Dengan pengaturan default, Minggu = 1 dan Sabtu = 7.
Trik untuk rumus ini adalah menyusun larik tanggal yang dapat Anda masukkan ke dalam fungsi WEEKDAY. Ini dilakukan dengan ROW with INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW mengartikan tanggal yang digabungkan sebagai nomor baris dan mengembalikan array seperti ini:
(43346;43347;43348;43349;43350;43351;43352)
Setiap angka dalam larik mewakili tanggal. Fungsi WEEKDAY kemudian mengevaluasi larik dan mengembalikan larik nilai hari kerja:
(2;3;4;5;6;7;1)
Angka-angka ini sesuai dengan hari dalam seminggu setiap tanggal. Mereka disediakan untuk fungsi MID sebagai argumen nomor awal, bersama dengan nilai di D6, "0888884" untuk teks:
MID("0888884",(2;3;4;5;6;7;1),1)
Karena kami memberikan MID array nomor awal, ia mengembalikan array hasil seperti ini:
("8";"8";"8";"8";"8";"4";"0")
Nilai ini sesuai dengan jam kerja setiap hari dari tanggal mulai hingga tanggal akhir. Perhatikan nilai dalam larik ini adalah teks, bukan angka. Untuk mengonversi ke angka sebenarnya, kami mengalikan dengan larik kedua yang dibuat untuk mengelola hari libur, seperti yang dijelaskan di bawah ini. Operasi matematika memaksa teks menjadi nilai numerik.
Liburan
Untuk menangani hari libur, kami menggunakan ISNA, MATCH, dan rentang bernama "hari libur" seperti ini:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ekspresi ini menggunakan MATCH untuk menemukan tanggal yang berada di dalam rentang bernama liburan menggunakan array yang sama dari tanggal yang dihasilkan di atas dengan TIDAK LANGSUNG dan BARIS. MATCH mengembalikan angka saat hari libur ditemukan dan kesalahan # N / A jika tidak. Fungsi ISNA "membalik" hasil sehingga TRUE mewakili hari libur dan FALSE mewakili bukan hari libur. ISNA mengembalikan larik atau hasil seperti ini:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Terakhir, kedua larik dikalikan satu sama lain di dalam SUMPRODUCT. Operasi matematika memaksa TRUE dan FALSE menjadi 1 dan nol, dan nilai teks dalam larik pertama menjadi nilai numerik (seperti dijelaskan di atas), jadi pada akhirnya kita memiliki:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Setelah perkalian, kami memiliki satu larik di dalam SUMPRODUCT yang berisi semua jam kerja dalam rentang tanggal:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT lalu menjumlahkan semua item dalam larik dan mengembalikan hasil dari 36.
