Calculating time differences regardless of date

[gruntingmonkey]

I am trying to calculate in minutes how far two times are apart regardless of what day it is. In the image attached, I cant work out the calculation for column C. In theory, the minutes difference will never be more than 720 (12 hours times 60 minutes).

Actual Time	Guessed Time	Minutes difference
23:59	00:01	2
	01:01	62
	02:01	122
	12:00	719
	18:00	359

 

[etaf]

will the time always be clockwise the time in A1 - ie clockwise on a clock

for example - if it was 20:00 - then if a guess was
20:01 = thats 1 min
07:59 - then 718
and will never be later than 08:01 ????

so can never be
20:01
19:59

 

[Dave Patton]

a guess; parts of the information are not clear

Time.xlsm
	A	B	C	D	E
1
2	23:59	00:01	00:02	2	2
3		01:01	01:02	62	62
4		02:01	02:02	122	122
5		12:00	12:01	721	720
6		18:00	18:01	1081	720
7
12a

Cell Formulas
Range	Formula
C2:C6	C2	=B2-$A$2+($A$2>B2)
D2:D6	D2	=C2*24*60
E2:E6	E2	=MIN(720,C2*24*60)

 

[gruntingmonkey]

will the time always be clockwise the time in A1 - ie clockwise on a clock

for example - if it was 20:00 - then if a guess was
20:01 = thats 1 min
07:59 - then 718
and will never be later than 08:01 ????

so can never be
20:01
19:59

No - the time won't always be one way. It's a guessing game. Guess the closest time. So you can guess any time in 24 hours. In your example, if the time was 20:00 then a guess of either 20:01 or 19:59 would both be 1 minute out.

 

[gruntingmonkey]

a guess; parts of the information are not clear

Time.xlsm
	A	B	C	D	E
1
2	23:59	00:01	00:02	2	2
3		01:01	01:02	62	62
4		02:01	02:02	122	122
5		12:00	12:01	721	720
6		18:00	18:01	1081	720
7
12a

Cell Formulas
Range	Formula
C2:C6	C2	=B2-$A$2+($A$2>B2)
D2:D6	D2	=C2*24*60
E2:E6	E2	=MIN(720,C2*24*60)

The bottom two rows don't show the correct result. Its not a case of a minimum of 720. In terms of time it's how far away the times are apart from that original ctual time. It will never be more than 12 hours (720 minutes). Apologies, I'm not sure if I'm explaining it appropriately.

It's a guessing game. Guess the closest time. So you can guess any time in 24 hours. In your example, if the time was 20:00 then a guess of either 20:01 or 19:59 would both be 1 minute out.

 

[pjmorris]

try this: =MIN(A2-B2,(1+A2-B2)) where A2 has the initial value and B2 the guess. It assumes that the cells are formatted for times and contain times (ie no date element).

HTH.

 

[gruntingmonkey]

try this: =MIN(A2-B2,(1+A2-B2)) where A2 has the initial value and B2 the guess. It assumes that the cells are formatted for times and contain times (ie no date element).

HTH.

Thanks for looking into for me. It doesn't appear to give the result I'm after though.

Actual Time	Guessed Time	Minutes difference	PJMorris
23:59	00:01	2	23:58:00​
	01:01	62	22:58:00​
	02:01	122	21:58:00​
	12:00	719	11:59:00​
	18:00	359	05:59:00​

I have found a solution though using @Dave Patton solution though using his calculations in column C & D.
Everything over 720 is removed the sum and therefore kind of counts backwards. I'm sure there must be a simpler calculation but I'm just happy I found something that works!

=SUM(720-ABS(720-SUM(SUM(B2-$A$2+($A$2>B2))*24*60)))

 

[pjmorris]

Hi, apologies. The correct formula is =MIN($A$1-B1,(1+B1-$A$1)) and you need a custom format of [m] to show the total number of minutes. Alternatively and to more easily use the number of minutes in a calculation format the cell as 'general' which is the default and use =MIN($A$1-B1,(1+B1-$A$1))*24*60.

That said, there is usually more than one way of solving the problem.

Regards

 

[Dave Patton]

Do you require so many "Sum" functions?
Consider =720-ABS(720-(B2-$A$2+($A$2>B2))*24*60)
or =MIN($A$2-B6,B6-$A$2+($A$2>B6))*24*60

With Excel 365
=LET(x,(B2-$A$2+($A$2>B2))*24*60,IF(x<=720,x,720-(x-720)))