The problem is that you are using time of day for the start and ends times without coupling it with a date. In the example of Coordinator, the end time of 12:40 AM comes before the start time of 4:00 PM, so the arithmetic doesn't work.
To fix this, you have to calculate the end time so that it is in the following day if it goes past midnight. Change the formula in K5 to this
and copy down the column. Your formula was more complicated than it needed to be so this simplifies it. Also, instead of just using TIME to determine a time, it adds the time increment to the start time so that if it rolls past midnight, it counts an extra day. Your display format just shows the time so this will look the same to the user as what you have now, but your results farther to the right will be correct.
Bookmarks