This solution modifies two of the formulae found in this article.
Notes: The calculation of G2 and H2 is respectively changed to: =A2+B2 and =C2+D2
Two cells have been added: X2 is the Peak Start and Y2 is the Peak End, this allows flexibility, as opposed to hard coding, should these times change at a later date.
The first modified formula, which is the main formula in the article, calculates the peak hours and reads:
Formula:
=IF(OR(Y$2<X$2,H2<G2),0,
(NETWORKDAYS(G2,H2,P2)
-(NETWORKDAYS(G2,G2,P2)
*IF(MOD(G2,1)>Y$2,1,
(MAX(X$2,MOD(G2,1))-X$2)
/(Y$2-X$2)))
-(NETWORKDAYS(H2,H2,P2)
*IF(MOD(H2,1)<X$2,1,
(Y$2-MIN(Y$2,MOD(H2,1)))
/(Y$2-X$2))))
*(Y$2-X$2)*24)
The second formula takes a formula, called the 24 hour variation in the article, and subtracts the peak hours to calculate non-peak hours and reads:
Formula:
=IF(G2>H2,0,(NETWORKDAYS(G2,H2,P2)
-NETWORKDAYS(G2,G2,P2)*MOD(G2,1)
-NETWORKDAYS(H2,H2,P2)*(1-MOD(H2,1)))*24)-R2
Let me know if you have any questions.
Bookmarks