Formula generica
=(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)
Sommario
Per calcolare le ore di lavoro totali tra due date e ore, è possibile utilizzare una formula basata sulla funzione NETWORKDAYS. Nell'esempio mostrato, E5 contiene questa formula:
=(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)
dove "inferiore" è l'intervallo denominato H5 e "superiore" è l'intervallo denominato H6.
Nota: questo esempio è stato ispirato da una sfida di formula su Chandoo e una soluzione più completa fornita dal maestro di formule Barry Houdini sul forum MrExcel.
Spiegazione
Questa formula calcola le ore lavorative totali tra due date e orari, che si verificano tra un tempo "inferiore" e uno "superiore". Nell'esempio mostrato, l'ora inferiore è 9:00 AM e l'ora superiore è 5:00 PM. Questi appaiono nella formula come gli intervalli denominati "inferiore" e "superiore".
La logica della formula consiste nel calcolare tutte le ore lavorative possibili tra le date di inizio e di fine, incluse, quindi annullare tutte le ore della data di inizio che si verificano tra l'ora di inizio e l'ora inferiore e le ore della data di fine che si verificano tra l'ora di fine e l'ora superiore.
La funzione NETWORKDAYS gestisce l'esclusione dei fine settimana e dei giorni festivi (se forniti come intervallo di date). Puoi passare a NETWORKDAYS.INTL se la tua pianificazione prevede giorni lavorativi non standard.
Formattazione dell'output
Il risultato è un numero che rappresenta le ore totali. Come tutti i tempi di Excel, sarà necessario formattare l'output con un formato numerico adatto. Nell'esempio mostrato, stiamo usando:
(h):mm
Le parentesi quadre impediscono a Excel di scorrere quando le ore sono maggiori di 24. In altre parole, consentono di visualizzare ore maggiori di 24. Se hai bisogno di un valore decimale per le ore, puoi moltiplicare il risultato per 24 e formattare come un numero regolare.
Versione semplice
Se l'ora di inizio e l'ora di fine si verificano sempre tra l'ora inferiore e quella superiore, puoi utilizzare una versione più semplice di questa formula:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Nessuna ora di inizio e ora di fine
Per calcolare le ore di lavoro totali tra due date, supponendo che tutti i giorni siano giorni lavorativi pieni, puoi utilizzare una formula ancora più semplice:
=NETWORKDAYS(start,end,holidays)*hours
Vedere la spiegazione qui per i dettagli.
