# How to calculate the difference between two times accurately across midnight? [duplicate]

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.

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.

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?

• 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. Commented Jan 23, 2020 at 20:27