Counting hours between set times across midnight

There is no lack of threads regarding time calculations over crossing over between midnight. That in itself is no issue, I've tried =MOD(C2-B2; 1) and many other ways to calculate this and followed many guides.

I am creating an automated way to calculate hours passed with different rates depending on WHEN they occur.
A2: Date
B2: Start time
C2: End time
F2: Daytime during the weekdays between 6AM and 6PM
G2: Evenings during the weekdays between 6PM and 12PM
H2: Nights during the weekdays between 12PM and 6AM
J2: Weekends 12AM to 12PM

Example for G2:
=IF(AND(WEEKDAY(A2; 2)<=5; OR(HOUR(B2)<18; HOUR(C2)>18));
MAX(MIN(HOUR(C2) + MINUTE(C2)/60; 24) - MAX(HOUR(B2) + MINUTE(B2)/60; 18); 0); 0)

This works, but as soon as the time goes over midnight, for an example:
6PM Start time
2AM End time
G2(hours counted 6PM to 12PM) = 0

Anyone knows a good way to deal with crossing over midnight? Maybe there is a much easier way to go about this.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try

D2

PHP Code: 

=MOD(C2+(C2<B2)-B2, 1) 


E2

PHP Code: 

=SUMPRODUCT((F2:I2*{315,405,450,490})) 


F2

PHP Code: 

=IF(WEEKDAY($A2,2)<6,MIN($C2+($C2=0),18/24)-MAX($B2,6/24),0)*24 


G2

PHP Code: 

=IF(WEEKDAY($A2,2)<6,MIN($C2+($C2=0),1)-MAX($B2,6/24),0)*24-F2 


H2

PHP Code: 

=IF(WEEKDAY($A2,2)<6,IF($C2<$B2,MIN(6/24,$C2), MIN(6/24,$C2)-MIN(6/24,$B2))*24,0) 


I2

PHP Code: 

=IF(WEEKDAY($A2,2)>5,IF(C2>B2,C2-B2,1-B2),0)*24 


F2 then drag down:

Please Login or Register  to view this content.