If function help please

[Dcumby]

I have 3 columns with times displayed in each and need to determine the closest in time to that in cell A1

Example

A1 B1 C1
08:35:47 17:05:45 08:36:27
11:14:47 11:15:06 09:03:18
15:56:26 15:56:41 14:55:10

Thanks

Dcumby

[oeldere]

It's always a value above the value in A1?

If so, you can use this one.

Please Login or Register  to view this content.

[Dcumby]

Yeah will always be above the value in A1.

I need it to return the nearest in time in the same line only:

So:

08:35:47 17:05:45 08:36:27

I need it to return or highlight, show me in some way which is nearest in time to that in cell A1. I have 17000 lines like this I need to determine.

Any help will be appreciated as you can tell.

Thanks

[benishiryo]

hi Dcumby, welcome to the forum. assuming your data starts from A1, select the range you want to apply to (maybe B1:C17000)
in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
=$A1+MIN(ABS($A1-$B1),ABS($A1-$C1))=B1

format to the colour you desire