Forum Discussion
Excel conditional formatting
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....
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.
- 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.,
We don't need your actual date. You may generate sample Excel with fake data, that's 5-10 minutes job.
- 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?- 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.
