Selected Answer
Blank cells wouldn't be so much of a problem. My immediate problem is that I can't tell from your question in which cell, on which of the several worksheets in your workbook, you want a total, nor which cells are supposed to be evaluated.
However, I saw that your times are text. It's possible to convert text to times but that doesn't make much sense since you took so much trouble to convert times to text before. Please [LINK URL="http://www.cpearson.com/excel/datetime.htm"]learn about dates and times here[/LINK]. You will also need to [LINK URL="https://www.excel-easy.com/basics/format-cells.html"]learn about cell formats[/LINK] and this is probably as good a site to do that as any alterantive.
The gist of it all is that, in Excel, what you see in the cell is different from what the cell contains. There is a Formula which produces Value, and that Value is displayed in a specified Format. Your formulas attempt to produce a Value that is equal to the display Format and that isn't possible where the display is text and the Value is a number. Dates and Times must be numbers because you want to do calculations with them. Therefore they can't be text.
You will learn enough at the two sites I have recommended above to advance your project a whole lot. However, I want to alert you to one trap that you might want to avoid. The nature of the trap is the difference between 11pm and 1am. That could be 22 hours or just 2 hours. The simple and easy way to get the correct resut is to include dates in your time. So, your calculation ends up like Tomorrow.01:00 - Today.23:00. Don't worry about the display format. Excel calculates in 24-hour terms but you can display the result any way you like, including AM/PM and without the date. If you don't want it to appear in the cell the cell format will take care of that. The important thing is to have it available in the cell's Value so that you can do correct calculations.
