I have written a formula to allocate minutes for a case duration to each hour (columns E through AB). However, if the case goes over into the next day it evaluates 12pm as being < 1am and makes the statement false. In the attached spreadsheet, row 2 shows this. Row 3 allocates the minutes correctly. Is there another way to allocate minutes by hour of day?
| Test.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
| 1 | Status | Case Duration Minutes | Actual Start Time | Actual Out Time | 12:00 AM | 1:00 AM | 2:00 AM | 3:00 AM | 4:00 AM | 5:00 AM | 6:00 AM | 7:00 AM | 8:00 AM | 9:00 AM | 10:00 AM | 11:00 AM | 12:00 PM | 1:00 PM | 2:00 PM | 3:00 PM | 4:00 PM | 5:00 PM | 6:00 PM | 7:00 PM | 8:00 PM | 9:00 PM | 10:00 PM | 11:00 PM | ||
| 2 | Incorrect | 61 | 11:09:00 PM | 12:10:00 AM | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 3 | Correct | 601 | 1:09:00 PM | 11:10:00 PM | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 51.00 | 60.00 | 60.00 | 60.00 | 60.00 | 60.00 | 60.00 | 60.00 | 60.00 | 60.00 | 10.00 | ||
Sheet 1 | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:AB3 | E2 | =IF(AND(HOUR($C2)=HOUR(E$1),HOUR($D2)=HOUR(E$1)),$B2,IF(OR($C2>E$1+(1/24),$D2<E$1),0,IF(AND(E$1>$C2,E$1+1/24<$D2),60,IF(HOUR(E$1)<=HOUR($C2),24*60*(MAX(0,E$1+(1/24)-$C2)),24*60*($D2-E$1))))) |
