Excel formülü: Tarihler ve saatler arasındaki çalışma saatlerini öğrenin -

İçindekiler

Genel formül

=(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)

Özet

İki tarih ve saat arasındaki toplam çalışma saatini hesaplamak için, NETWORKDAYS işlevine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, E5 şu formülü içerir:

=(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)

burada "alt", adlandırılan aralık H5 ve "üst", adlandırılmış aralık H6'dır.

Not: Bu örnek, Chandoo'daki bir formül mücadelesinden ve MrExcel forumunda formül ustası Barry Houdini tarafından sağlanan daha eksiksiz bir çözümden ilham almıştır.

Açıklama

Bu formül, "alt" ve "üst" saatler arasında gerçekleşen iki tarih ve saat arasındaki toplam çalışma saatlerini hesaplar. Gösterilen örnekte, alt saat 9:00 AM ve üst saat 17:00. Bunlar formülde "alt" ve "üst" olarak adlandırılan aralıklar olarak görünür.

Formülün mantığı, başlangıç ve bitiş tarihleri dahil olmak üzere tüm olası çalışma saatlerini hesaplamak, ardından başlangıç tarihi ile başlangıç saati ile alt saat arasında gerçekleşen tüm saatleri ve bitiş tarihinde meydana gelen saatleri geri almaktır. bitiş zamanı ve üst zaman.

NETWORKDAYS işlevi, hafta sonları ve tatillerin hariç tutulmasını işler (tarih aralığı olarak sağlandığında). Programınızda standart olmayan çalışma günleri varsa, TAMİŞGÜNÜ.ULUSL'a geçebilirsiniz.

Çıktı biçimlendirme

Sonuç, toplam saatleri temsil eden bir sayıdır. Tüm Excel zamanlarında olduğu gibi, çıktıyı uygun bir sayı biçimiyle biçimlendirmeniz gerekecektir. Gösterilen örnekte şunları kullanıyoruz:

(h):mm

Köşeli parantezler Excel'in saatler 24'ten büyük olduğunda dönmesini engeller. Diğer bir deyişle, 24'ten büyük saatlerin görüntülenmesini mümkün kılar. Saatler için ondalık bir değere ihtiyacınız varsa, sonucu 24 ile çarpabilir ve normal numara.

Basit versiyon

Başlangıç ve bitiş zamanları her zaman alt ve üst zamanlar arasında olacaksa, bu formülün daha basit bir versiyonunu kullanabilirsiniz:

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

Başlangıç ve bitiş zamanı yok

İki tarih arasındaki toplam çalışma saatini hesaplamak için, tüm günlerin dolu iş günleri olduğunu varsayarak, daha da basit bir formül kullanabilirsiniz: