Formula generik
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Ringkasan
Untuk mengira jumlah waktu kerja antara dua tarikh dan waktu, anda boleh menggunakan formula berdasarkan fungsi NETWORKDAYS. Dalam contoh yang ditunjukkan, E5 mengandungi formula ini:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
di mana "bawah" adalah julat bernama H5 dan "atas" adalah julat bernama H6.
Catatan: contoh ini diilhamkan oleh cabaran formula pada Chandoo, dan penyelesaian yang lebih lengkap yang disediakan oleh master formula Barry Houdini di forum MrExcel.
Penjelasan
Rumus ini mengira jumlah waktu kerja antara dua tarikh dan waktu, yang berlaku antara waktu "rendah" dan "atas". Dalam contoh yang ditunjukkan, waktu yang lebih rendah adalah 9:00 AM dan waktu yang lebih tinggi adalah 5:00 PM. Ini muncul dalam formula sebagai rentang bernama "bawah" dan "atas".
Logik formula adalah untuk mengira semua waktu kerja yang mungkin antara tarikh mula dan tarikh akhir, termasuk, kemudian mundur setiap jam pada tarikh mula yang berlaku antara waktu mula dan waktu yang lebih rendah, dan setiap jam pada tarikh akhir yang berlaku antara masa tamat dan masa atas.
Fungsi NETWORKDAYS menangani pengecualian hujung minggu dan cuti (apabila disediakan sebagai julat tarikh). Anda boleh beralih ke NETWORKDAYS.INTL jika jadual anda mempunyai hari bekerja yang tidak standard.
Memformat output
Hasilnya adalah nombor yang mewakili jumlah jam. Seperti semua masa Excel, anda perlu memformat output dengan format nombor yang sesuai. Dalam contoh yang ditunjukkan, kami menggunakan:
(h):mm
Tanda kurung siku menghentikan Excel daripada berpusing apabila jam lebih besar daripada 24. Dengan kata lain, ia memungkinkan untuk memaparkan jam lebih besar daripada 24. Sekiranya anda memerlukan nilai perpuluhan selama berjam-jam, anda boleh mengalikan hasilnya dengan 24 dan memformat sebagai nombor biasa.
Versi ringkas
Sekiranya waktu mula dan akhir selalu berlaku antara waktu yang lebih rendah dan yang lebih tinggi, anda boleh menggunakan versi formula yang lebih mudah ini:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Tiada masa mula dan waktu tamat
Untuk mengira jumlah jam kerja antara dua tarikh, dengan anggapan semua hari adalah hari kerja penuh, anda boleh menggunakan formula yang lebih mudah:
=NETWORKDAYS(start,end,holidays)*hours
Lihat penjelasan di sini untuk perincian.
