Search an array located by referencing a date match

[240racr]

Hello all; new to the board.

I'm really stuck on a problem. I'm trying to write a formula that will be used in conditional formatting. I have a calendar that uses macros and self populates the days when selecting a year using a spin button. I want to conditional format each day to change color based on the word 'down' located on another sheet. The other sheet has 10 years worth of dates and time slots; so it's a rather large sheet. I need to find the date (on the data sheet) that matches the date on the calendar, then based on that location it would search a predetermined area for 'down' based on the location of the found matching date. Yeah, probably not the best explanation...

Here is what I came up with (which obviously doesn't work) by just looking up how to use different functions.
=COUNTIF(OFFSET(MATCH(B11,TimeStamp!A1:A6955,0),2,1,17,3),"*down*")

• B11 is the cell that contains self populated date
  
• TimeStamp!A1:A6955 is column that contains dates & times for the first year. The next year starts on F1.

Any help is greatly appreciated.

 

[FormR]

The tables go across and down. Years go to the right and days go down.

ie. Dates are in the following cells and continue through 2025 and 365 days/year (plus leap year days)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]F[/TD]
[TD]K[/TD]
[TD]P....[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2016[/TD]
[TD]1/1/2017[/TD]
[TD]1/1/2018[/TD]
[TD]1/1/2019...[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1/2/2016[/TD]
[TD]1/2/2017[/TD]
[TD]1/2/2018[/TD]
[TD]1/2/2019[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]1/3/2016[/TD]
[TD]1/3/2017[/TD]
[TD]1/3/2018[/TD]
[TD]1/3/2019[/TD]
[/TR]
[TR]
[TD]59...[/TD]
[TD]1/4/2016...[/TD]
[TD]1/4/2017...[/TD]
[TD]1/4/2018...[/TD]
[TD]1/4/2019...[/TD]
[/TR]
</tbody>[/TABLE]

That table has helped: You could give this lengthy formula a try.

=COUNTIF(OFFSET(INDEX(INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR(B11),1,1),TimeStamp!$A$2:$AW$2,0)),MATCH(B11,INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR(B11),1,1),TimeStamp!$A$2:$AW$2,0)),0)),2,1,17,3),"*down*")

 

[240racr]

That table has helped: You could give this lengthy formula a try.

=COUNTIF(OFFSET(INDEX(INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR(B11),1,1),TimeStamp!$A$2:$AW$2,0)),MATCH(B11,INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR(B11),1,1),TimeStamp!$A$2:$AW$2,0)),0)),2,1,17,3),"*down*")

That is so freaking amazing!!!! That worked and I don't 100% understand what it's doing. THANK YOU!!

I just had to change the following parts for each cell across, then format paint the rows down on my calendar. Now the calendar works perfectly!

=COUNTIF(OFFSET(INDEX(INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($B11),1,1),TimeStamp!$A$2:$AW$2,0)),MATCH($B11,INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($B11),1,1),TimeStamp!$A$2:$AW$2,0)),0)),2,1,17,3),"*down*")

=COUNTIF(OFFSET(INDEX(INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($C11),1,1),TimeStamp!$A$2:$AW$2,0)),MATCH($C11,INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($C11),1,1),TimeStamp!$A$2:$AW$2,0)),0)),2,1,17,3),"*down*")

=COUNTIF(OFFSET(INDEX(INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($D11),1,1),TimeStamp!$A$2:$AW$2,0)),MATCH($D11,INDEX(TimeStamp!$A$2:$AW$6955,0,MATCH(DATE(YEAR($D11),1,1),TimeStamp!$A$2:$AW$2,0)),0)),2,1,17,3),"*down*")