I have two lists of time, one a set of starts and the other a set of ends, I want to calculate the duration between them and sum them.When the end time goes past midnight, I experience a problem. I can calculate the difference between the times correctly, but the sum of the duration is incorrect.Here is the data and the result:Here are my formulas:
Calculate time difference between times past midnight
google spreadsheets
You have two problems, you are neither displaying, nor calculating the duration correctly.Quick Answer:Format your duration column as Format > number > duration.Use the formula =B2-A2+(B2<A2) to calculate a duration effectively.Formulas:Results:Explanation:The duration is being displayed as a time and not a duration. Here you can see the same difference, one formatted as automatic, which defaults to time, the other is formatted as a duration:The formula =B2-A2+(B2<A2) returns the difference between the two times, and adds 1 day if minuend is less than the subtrahend, it adds 0 days otherwise. 