your question makes a lot of sense from experience.. many people struggle with this.. Times are a fraction of 1 so 11pm = 0,958333 6am = 0,25
and Excel cannot calculate with negative time. there are several ways to go around the problem but the basic Idea is to make the endtime greater then the startvalue.
If starttime is in a1 and endtime is in B1 then I like to use
The --(A1>B1) is a trick to convert a True or false statement to a 0 or 1.
So if The starttime (11pm) is greater then end time(6am) it the statment is true or value 1 the sum then becomes 1+0,25-0,958333 = 0,2966667 or 7:00 hours
Another often used formula is
which comes down to exactly the same as the first solution but might be easier to read / understand what is happening.
Bookmarks