I've seen this answer on a related question which is an excellent simple solution to calculating the difference between two times. However I've run into an issue when trying to calculate the difference between midnight and times which are either after midnight or before midnight. I'm sure the fix is obvious, but I'd love to get some advice. Below is what I'm trying to do: work out the difference between a target sleep time and actual sleep time.

enter image description here

The fifth column is the result I want - showing either a positive or negative time duration. I can't work out how to achieve this though.

Below is Morgoth's solution in the linked question, which successfully shows the difference between two times when the first time is before midnight and the second time is before or after midnight.

enter image description here

As I've shown above, this solution doesn't work for my scenario - subtracting 23:30 from 00:00, for example, gets 23:30:00, whereas I want it to return -0:30:00 (or 0:30:00. The polarity doesn't matter, so long as it's consistent which is positive and negative.)

Is there a simple fix to do this?

EDIT: Here's a link to an example sheet outlining the problem.

  • Please share a test sheet with sample data and desired results. In your case (since dealing with dates/times which can be tricky) a screenshot is not much help. – marikamitsos Jan 23 '20 at 20:27
  • Sure, here's the link: docs.google.com/spreadsheets/d/… – Lou Jan 23 '20 at 20:32
  • Hm, I get the idea that you were suggesting in the spreadsheet, but I'm struggling to figure out how to implement it. Will wait til you post your answer to discuss. – Lou Jan 23 '20 at 20:53