Using VB to address hours worked past midnight

Another off-beaten topic, but most of my googling using VB tag leads to excel formulas, and I can't figure out how to convert to VB.

Anyway, very simple. I need to calculate hours worked. Some ending shifts will cross midnight. No shift will start between midnight and 5am.

Also, I am using the following code, but I have no idea how it works (specifically, the worksheetfunction.sum). It just does. The times that the code is looking at are stored as text values. An example formula is "=IF($L8="","",TEXT($I8,"H:MM AM/PM"))"

Please Login or Register  to view this content.

The First If says that if the shift start time is less than the employee's availability start time, then there is an availability conflict
The Second If says that if the shift end time is after the employee's availability end time, then there is an availability conflict
That works great if the end time is not midnight or later.
The next If statement needs to address the issue if the scheduled end time is after midnight.

The remarked lines are ones I have been experimenting with but do not work

ALSO, I need to also address how to calculate hours worked when a shift crosses over midnight.

Sample Attached

Sample Schedule.xlsx

Thanks in advance.

I took them out because of sensitive info

Also, I double remarked a line in the code I entered in the OP.....it is not working code...another failed experiment.