John,

I am not seeing that. I just adjusted my data to make midnight the lowest
amount, and it is returning 23:00.

Odd or what?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
Thanks Bob, yes it appears I'm subtracting 60 mins from 12:00am which
gives
a negative, i.e. anywhere I expect an 11:00pm closing time

Strange thing on your formula Bob is that its correct when closing time
(or
last sales hour) is 12.00am or latter, but its 1 hour out when the closing
time (or last sale hour) is before 12:00am eg. Expect to see 11:00pm, but
formula returns 12:00am

"Bob Phillips" wrote in message
...
The min is clearly less than 1 hour, so what do you want to do in those
circumstances, zeroise the result, or take the absolute value.

If the former, which makes mores sense to me, use


=MAX(TIME(1,0,0),INDEX($A18:$A33,MATCH(MIN(C18:C33 ),C18:C33,0)))-(60/1440)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
How can I correct this Time formula which says in the Tag that it
produces
a
negative Result and hence displays in cell as ####

=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)

What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the
value
selected in A. This in effect will give me my closing Times i.e. find
the
cell with Zero Sales then subtract 1 hour to find what must be the
closing
time (assuming of course that there is at least ?1 of sales per hour
while
open).

My cells in A are formatted as h:mm AM/PM, as is the format in the
formula
cell

Thanks