Finding the time value closest to zero (with 1904 date system)

[Joe94]

I am trying to find a formula that, for each row, will display the value of the cell which is closest to 0. In order to use negative time values I have had to use the 1904 date system which I believe is disrupting the results when Excel calculates the minimum value. I have currently tried something like =MIN(ABS(A1),ABS(A2)) but this is not working. Does anyone have any suggestions as to how I can go about fixing this?

A	B
00:00:50.799	00:00:50.305
00:00:00.242	00:00:00.000
-00:00:00.860	-00:00:00.473
-00:00:01.034	-00:00:00.188
-00:00:01.266	-00:00:00.422
-00:00:00.598	00:00:00.409

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thanks

 

[Marcelo Branco]

Maybe

C1 copied down
=MIN(0+SUBSTITUTE(A1,"-",""),0+SUBSTITUTE(B1,"-",""))

format the results: hh:mm:ss.000

M.

 

[arthurbr]

What's wrong with =MIN(ABS(A1),ABS(B1)) ?
In your example,I replaced A2 with B1 as you stated you are looking for the closest to zero for each row
Or are they text looking like numbers?

 

[Joe94]

Thanks for your replies.

The values are currently already in the format hh:mm:ss.000. Although the values look as I have put above, in the formula bar the top row reads as:

00:00:51

02/01/1904 00:00:50

<tbody>
</tbody>

so when I press "Show Formulas" they become:

0.000587951388888889	1.0005787037037

<tbody>
</tbody>

which is why I assume the minimum function isn't working. As I said previously, the 1904 date format was used to allow negative time values.

Hope this clears things up.

Thanks

 

[Joe94]

Oh and I forgot to say but Marcelo your suggestion didn't work sadly.

 

[Marcelo Branco]

Oh and I forgot to say but Marcelo your suggestion didn't work sadly.

hmm...worked for me (my Excel version uses comma as decimal separator)

	A​	B​	C​
1​	00:00:50,799​	00:00:50,305​	00:00:50,305​
2​	00:00:00,242​	00:00:00,000​	00:00:00,000​
3​	-00:00:00,860​	-00:00:00,473​	00:00:00,473​
4​	-00:00:01,034​	-00:00:00,188​	00:00:00,188​
5​	-00:00:01,266​	-00:00:00,422​	00:00:00,422​
6​	-00:00:00,598​	00:00:00,409​	00:00:00,598​

M.

 

[Joe94]

There must be something wrong with my data then I'll have to investigate further. Thanks for your help anyway.

 

[shg]

This worked fine for me:

	A​	B​	C​	D​
2​	00:00:50.799​	00:00:50.305​	00:00:50.305​	C2: =IF(ABS(A2) < ABS(B2), A2, B2)
3​	00:00:00.242​	00:00:00.000​	00:00:00.000​
4​	00:00:00.860​	-00:00:00.473​	-00:00:00.473​
5​	-00:00:00.860​	-00:00:00.473​	-00:00:00.473​
6​	-00:00:01.034​	-00:00:00.188​	-00:00:00.188​
7​	-00:00:01.266​	-00:00:00.422​	-00:00:00.422​
8​	-00:00:00.598​	00:00:00.409​	00:00:00.409​

It might be easier to convert everything to decimal seconds.

 

[Marcelo Branco]

This worked fine for me:

	A​	B​	C​	D​
2​	00:00:50.799​	00:00:50.305​	00:00:50.305​	C2: =IF(ABS(A2) < ABS(B2), A2, B2)
3​	00:00:00.242​	00:00:00.000​	00:00:00.000​
4​	00:00:00.860​	-00:00:00.473​	-00:00:00.473​
5​	-00:00:00.860​	-00:00:00.473​	-00:00:00.473​
6​	-00:00:01.034​	-00:00:00.188​	-00:00:00.188​
7​	-00:00:01.266​	-00:00:00.422​	-00:00:00.422​
8​	-00:00:00.598​	00:00:00.409​	00:00:00.409​

<tbody>
</tbody>

It might be easier to convert everything to decimal seconds.

Weird, didn't work for me (comma as decimal separator)

	A​	B​	C​
1​
2​	00:00:50,799​	00:00:50,305​	00:00:50,305​
3​	00:00:00,242​	00:00:00,000​	00:00:00,000​
4​	-00:00:00,860​	-00:00:00,473​	#VALOR!​
5​	-00:00:01,034​	-00:00:00,188​	#VALOR!​
6​	-00:00:01,266​	-00:00:00,422​	#VALOR!​
7​	-00:00:00,598​	00:00:00,409​	#VALOR!​

M.

 

[shg]

Marcelo, try entering negative times as, for example

=-"00:00:00,473"