I'm trying to get Google Sheets functions to calculate the difference in minutes between two bedtimes and have been spinning my wheels for at least five hours on this. Here are four examples of what I'm trying to accomplish:

BEDTIME 1     BEDTIME 2     DIFF IN MINS
9:00 PM       9:15 PM       15
9:00 PM       10:00 PM      60
11:30 PM      1:00 AM       90
1:00 AM       11:00 PM      120

As you can see, the date doesn't figure at all. I apologize for not offering up code, but I've tried at least half a dozen approaches from other answers and they aren't working -- mainly, I expect, because most people are looking to find the time elapsed between the two times whereas I'm looking to determine "how much earlier" or "how much later" one bedtime is relative to another (always expressed as a positive value).

Any help would be appreciated. Thanks.

CodePudding user response：

Times are stored as numbers between 0 and 1. If you subtract two times and multiply the result by 24 x 60 = 1440 and format as a number you’ll get number of minutes. I think you’ll need something like:

=MIN(ABS(1440*(B1-A1)), ABS(1440*(B1-A1-1)), ABS(1440*(B1-A1 1)))

CodePudding user response：

Try to implement a modulus function in your code. It would basically do something like this:

If x = -5, then y = f(x) = – (-5) = 5, since x is less than zero

If x = 10, then y = f(x) = 10, since x is greater than zero

If x = 0, then y = f(x) = 0, since x is equal to zero

Therefore calculating how much time passed without negative values.