Feb 13 2022 05:33 AM
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
Feb 13 2022 06:15 AM
You may use conditional formatting rule with formula like
=COUNTIFS(
itemX, currentValue,
time, "<=" & thisTime + 1 hour
time, ">=" & thisTime - 1 hour
)
Feb 13 2022 10:03 AM
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.
Feb 13 2022 10:23 AM
Feb 13 2022 11:09 AM
Feb 13 2022 04:57 PM
Feb 14 2022 03:53 AM
Feb 14 2022 07:59 AM
Feb 14 2022 09:22 AM
We don't need your actual date. You may generate sample Excel with fake data, that's 5-10 minutes job.
Feb 14 2022 10:37 AM
Feb 14 2022 10:44 AM
Feb 14 2022 12:01 PM
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)
Feb 15 2022 03:11 AM