Minimum time between two times

[Bishonen]

Hi all,

cut to the chase: I have a column with timeframes:

12:30 - 19:00
09:00 - 18:00
11:15 - 15:30
05:00 - 00:00

Sometimes theres a textstring "Off" inbetween those times. Is there a easy way to get the minimum time between such cells? I tried the following:

Please Login or Register  to view this content.

The problem with that is, that if a cell ends with 00:00 the subtraction is negative and thus the minimum. I had to throw in a "IFERROR" statement in there since those "Off's" return #Value instead.

Thanks

[AndyLitch]

If you absolute the arithmetic it will always return a positive number

Try this

=MIN(ABS(TIME(MID(G4:G124,9,2),MID(G4:G124,12,2),0)-TIME(LEFT(G4:G124,2),MID(G4:G124,4,2),0)))

[Tony Valko]

How about showing a few examples and tell us what results you expect.