Originally Posted by
Paul
Hi FWD,
Well, they're not the prettiest formulas, but I think they cover any possible scenarios for your start and end times. This assumes your Start Time is in A2 and your End Time is in B2 (adjust any references of A2 and B2 to your actual cells, of course).
To calculate Day Hours:
=IF(OR(AND(A2<=TIME(6,0,0),B2>=TIME(6,0,0),B2<=TIME(22,0,0)),AND(A2>=TIME(22,0,0),B2>=TIME(6,0,0),B2<=TIME(22,0,0))),24*(B2-TIME(6,0,0)),IF(AND(AND(A2>=TIME(6,0,0),A2<=TIME(22,0,0)),AND(B2>=TIME(6,0,0),B2<=TIME(22,0,0))),24*(B2-A2),IF(AND(A2>=TIME(6,0,0),A2<=TIME(22,0,0),OR(B2<=TIME(6,0,0),B2>=TIME(22,0,0))),24*(TIME(22,0,0)-A2),IF(AND(A2<=TIME(6,0,0),B2>=TIME(22,0,0)),16,0))))
To calculate Night Hours:
=IF(AND(A2<=TIME(6,0,0),B2<=TIME(6,0,0)),(B2-A2)*24,IF(AND(A2<=TIME(6,0,0),B2>=TIME(6,0,0),B2<=TIME(22,0,0)),(TIME(6,0,0)-A2)*24,IF(AND(A2<=TIME(6,0,0),B2>=TIME(22,0,0)),(24*(B2-TIME(22,0,0))+24*(TIME(6,0,0)-A2)),IF(AND(A2>=TIME(6,0,0),A2<=TIME(22,0,0),B2>=TIME(22,0,0)),(B2-TIME(22,0,0))*24,IF(AND(A2>=TIME(6,0,0),A2<=TIME(22,0,0),B2<=TIME(6,0,0)),(B2-TIME(0,0,0))*24+2,IF(AND(A2>=TIME(22,0,0),B2<=TIME(6,0,0)),(24-((A2-B2))*24),IF(AND(A2>=TIME(22,0,0),B2>=TIME(6,0,0)),2-((A2-TIME(22,0,0))*24)+6,0)))))))
Maybe someone else has a better idea, but these seem to work. Hope that helps!
Bookmarks