conditional formatting formula help excel 2007

[Lromao]

I need to write a formula that will highlight all of the transactions that occur within 3-5 seconds of each other. My data cell contains a dated timestamp in this format: 9/30/2011 6:29:46 PM.

Thanks for the help in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

transactions that occur within 3-5 seconds of each other.

Does this mean the transactions have to be at least 3 seconds apart and less than or equal to 5 seconds apart?
That is, transactions that are 2 seconds apart do not get formatted?

 

[Lromao]

Sorry...I meant from 0-5 seconds. Less than 5 seconds apart.

We had an issue with duplicates and want to be sure that is no lOnger the case.

 

[Peter_SSs]

Are the data/time stamp values in chronological order?

This suggestion assumes that they are. To be sure it works for the first value in the list, the cell above that first value should be blank. I added a new row 2 between the heading and the first value but then hid that new row.

Try this Conditional Formatting approach.

Excel Workbook
	D
1	Time stamps
3	19/11/2011 2:23:06 PM
4	19/11/2011 2:23:10 PM
5	19/11/2011 2:23:10 PM
6	19/11/2011 2:23:12 PM
7	19/11/2011 2:23:20 PM
8	19/11/2011 2:23:28 PM
9	19/11/2011 2:23:29 PM
10	19/11/2011 2:23:34 PM
11	19/11/2011 2:23:44 PM
12	19/11/2011 2:23:58 PM
13	19/11/2011 2:24:10 PM
14	19/11/2011 2:24:12 PM
15	19/11/2011 2:24:36 PM
CF Near Times

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	1. / Formula is =MIN(ABS(D2-D3),ABS(D3-D4))<(5/86400)	Abc