Rumus Excel: Dapatkan jam kerja antara tanggal dan waktu -

Formula umum

=(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 menghitung total jam kerja antara dua tanggal dan waktu, Anda dapat menggunakan rumus berdasarkan fungsi NETWORKDAYS. Dalam contoh yang ditunjukkan, E5 berisi rumus 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 rentang bernama H5 dan "atas" adalah rentang bernama H6.

Catatan: contoh ini terinspirasi oleh tantangan formula di Chandoo, dan solusi yang lebih lengkap diberikan oleh master formula Barry Houdini di forum MrExcel.

Penjelasan

Rumus ini menghitung total jam kerja antara dua tanggal dan waktu, yang terjadi antara waktu "lebih rendah" dan "atas". Dalam contoh yang ditampilkan, waktu yang lebih rendah adalah 9:00 dan waktu yang lebih tinggi adalah 5:00. Ini muncul dalam rumus sebagai rentang bernama "bawah" dan "atas".

Logika rumusnya adalah menghitung semua kemungkinan jam kerja antara tanggal mulai dan akhir, inklusif, lalu mundur setiap jam pada tanggal mulai yang terjadi antara waktu mulai dan waktu yang lebih rendah, dan jam apa pun pada tanggal akhir yang terjadi antara waktu akhir dan waktu puncak.

Fungsi NETWORKDAYS menangani pengecualian akhir pekan dan hari libur (jika disediakan sebagai rentang tanggal). Anda dapat beralih ke NETWORKDAYS.INTL jika jadwal Anda memiliki hari kerja non-standar.

Memformat keluaran

Hasilnya adalah angka yang mewakili total jam. Seperti semua waktu Excel, Anda perlu memformat keluaran dengan format angka yang sesuai. Dalam contoh yang ditunjukkan, kami menggunakan:

(h):mm

Tanda kurung siku menghentikan Excel dari bergulir ketika jam lebih besar dari 24. Dengan kata lain, mereka memungkinkan untuk menampilkan jam lebih besar dari 24. Jika Anda membutuhkan nilai desimal untuk jam, Anda bisa mengalikan hasilnya dengan 24 dan memformat sebagai nomor biasa.

Versi sederhana

Jika waktu mulai dan berakhir akan selalu terjadi antara waktu yang lebih rendah dan lebih tinggi, Anda dapat menggunakan versi yang lebih sederhana dari rumus ini:

=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)

Tidak ada waktu mulai dan waktu berakhir

Untuk menghitung total jam kerja antara dua tanggal, dengan asumsi semua hari adalah hari kerja penuh, Anda dapat menggunakan rumus yang lebih sederhana: