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.
Bookmarks