nearest time value to 00:00

[sophiabc1]

Excel 2010. I have two columns in each row for time, in hh:mm, and would like to know how to get a third column to display whichever of the first two values is nearest to 00:00. The first two columns have negative time values too (ie. they represent time before and after High Tide). for example,

02:46	-09.39	display here the time (with sign) nearest to 00:00 (should be 02:46)
-01:23	08:35	(should be -01:23)
02:30	-09.25	(should be 02:30)

<tbody>
</tbody>

 

[Andrew Poulsom]

Assuming your times are serial times rather than text try:

=IF(ABS(A1)<ABS(B1),A1,B1)

 

[Marcelo Branco]

Assuming your times as text

A B C

02:46	-09:39	02:46
-01:23	08:35	-01:23
02:30	-09:25	02:30

<tbody>
</tbody>

Array formula in C1
=INDEX(A1:B1,MATCH(MIN(SUBSTITUTE(A1:B1,"-","")+0),SUBSTITUTE(A1:B1,"-","")+0,0))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

 

[Andrew Poulsom]

My formula was truncated by HTML parsing:

=IF(ABS(A1) < ABS(B1),A1,B1)

 

[sophiabc1]

Thank you both for your answers. Marcelo's formula worked for me, as I've just realised my two time columns were actually text (as I had used a formula in both those to get 'time' values, which were from real time values in previous columns! Apologies Andrew). Thanks again, Sophia