A couple of ideas to support your research into how Excel works with date/time serial numbers. These are the main things I have used to become more familiar with date/time data in excel:I'm keen to learn as well as fix problems here...
1) A page like this that explain how excel stores and uses date/time as serial numbers http://www.cpearson.com/Excel/datetime.htm
2) As a learning step, have a column/row adjacent to your date/time calculations that mirror the calculation without formatting (see example). Enter different times in A2 and A3, see what their underlying serial number/value is in B2 and B3, then see the result of the calculation in A4 and B4. You can also select A4 and execute the "Evaluate formula" command to step through the IF() function.
Those two ideas should help you understand what is going on.
Bookmarks