Formula generică
=(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)
rezumat
Pentru a calcula numărul total de ore de lucru între două date și ore, puteți utiliza o formulă bazată pe funcția NETWORKDAYS. În exemplul prezentat, E5 conține această formulă:
=(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)
unde „inferior” este intervalul numit H5 și „superior” este intervalul numit H6.
Notă: acest exemplu a fost inspirat de o provocare formulă pe Chandoo și de o soluție mai completă oferită de maestrul formulelor Barry Houdini pe forumul MrExcel.
Explicaţie
Această formulă calculează numărul total de ore de lucru între două date și ore, care apar între un timp „inferior” și „superior”. În exemplul prezentat, ora inferioară este 9:00 AM și ora superioară este 17:00. Acestea apar în formulă ca intervalele denumite „inferior” și „superior”.
Logica formulei este de a calcula toate orele de lucru posibile între datele de începere și de sfârșit, inclusiv, apoi să retragă orele la data de începere care apar între ora de începere și ora inferioară și orele la data de sfârșit care apar între timpul de sfârșit și timpul superior.
Funcția NETWORKDAYS gestionează excluderea weekend-urilor și a sărbătorilor (atunci când este furnizată ca interval de date). Puteți trece la NETWORKDAYS.INTL dacă programul dvs. are zile lucrătoare non-standard.
Formatarea ieșirii
Rezultatul este un număr care reprezintă numărul total de ore. Ca toate timpurile Excel, va trebui să formatați ieșirea cu un format de număr adecvat. În exemplul prezentat, folosim:
(h):mm
Parantezele pătrate împiedică rularea Excelului când orele sunt mai mari de 24. Cu alte cuvinte, acestea permit afișarea orelor mai mari de 24. Dacă aveți nevoie de o valoare zecimală pentru ore, puteți înmulți rezultatul cu 24 și formatarea ca număr regulat.
Versiune simplă
Dacă orele de început și sfârșit vor apărea întotdeauna între timpurile inferioare și cele superioare, puteți utiliza o versiune mai simplă a acestei formule:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Fără ora de început și ora de sfârșit
Pentru a calcula numărul total de ore între două date, presupunând că toate zilele sunt zile lucrătoare complete, puteți utiliza o formulă și mai simplă:
=NETWORKDAYS(start,end,holidays)*hours
A se vedea explicația aici pentru detalii.
