Excel conditional formatting

[eatonad]

I have a column that is time and a column with item x  I cannot have two of the same item in a two hour period.  I have used conditional formatting to highlight for a two hour block from 1-3,4-6,etc. works great except it lets me put a 3 and 4 of the same item x in the column.  I tried adding another conditional formatting but then it starts highlighting everything.  Is there any other way to format this to highlight a duplicate just within a six cell block of itself?

Time                   Item X

1                           g                   not ok                      OK

1                            c                      I                              I

2                            f                      I                               I 

2                                                    I                              I

3                             g                   Not ok                      I                       Not ok

3                                                                                   I                             I

4                             g                                                  OK                      Not ok

4

5

5

[#2]

@eatonad 

You may use conditional formatting rule with formula like

=COUNTIFS(
 itemX, currentValue,
 time, "<=" & thisTime + 1 hour
 time, ">=" & thisTime - 1 hour
)

[#3]

@Sergei Baklan 

I am trying to teach myself Excel and have been at it a couple days this is by far the hardest thing I am trying to do....

Will this work if item x is any value at any time?  Essentially If item x is a at 3 and 4 both need to be highlighted but not at 3 and 5 but if item x is b at 3 and 4 both b's need to be highlighted but not at 3 and 5, or if item x is c at 10 and 11 both c's  to be highlighted but not at 1 and 10. Sorry if this is confusing it is for me. I know what I want it to do I am just trying to figure out how to get it to do it.  If I need a condition for each Item X at each time that will not be fun.

[#4]

@eatonad 

It's better with sample. Could you please illustrate with something like this.

[#5]

I cannot screen shot or cut on this computer where I am doing the project as it is blocked for sensitive info. I put that formula in for my values of L is my column c and 2-49 and b is my time and it always highlights any duplicates still. I see that it does not on yours I will keep messing around with it. =COUNTIFS($L$2:$L$49,$L2,$B$2:$B$49,"<="&($B2+1),$B$2:$B$49,">="&($B2-1))>1 is the formula I am using.,

[#6]

2 to 49 for the time column? the number is not about the "hour"?

Back to your first post, why does time1-g and time3-g is not ok?
is "time1 to time3" a two-hour block?
and does it mean that "time2 to time4" a two-hour block too?

[#7]

You are correct that was supposed to be up on the second 2 in the 4th row. Each time frame is a 2 hour block 24 hours a day plus a header makes my rows 2-49 that need to highlight the square in a two hour time frame to acknowledge an overlap.

[#8]

so my excell is more like
Date time C D E F G H I J K L M N O P
0 x
0 z
1
1 y
2
2 x
3
3 x
4
4
All the way down to 23:00 and in this one X needs to highlight if x=0 and x=2 or x=2 and x=3 but not if x =0 and x=3

[#9]

@eatonad 

We don't need your actual date. You may generate sample Excel with fake data, that's 5-10 minutes job.

[#10]

@Sergei Baklan 

The formula is highlighting every one that is the same

[#11]

=COUNTIFS($L$2:$L$49,$L2,$B$2:B$49,"<="&($B2+1),$B$2:$B$49,">="&($B2-1))>1
This is the formula in case you cannot read it

[#12]

@eatonad 

As a minimum you shall use $B2+1/24 and $B2-1/24

since in column B you use time as the time (1:00 = 1/24 of the day), not as general numbers as in the post (1 hour, 2 hours, etc)

[#13]

Works perfectly thank you people so much and not just solving it explaining it so I can understand why.