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)

	A	F	K	P....
2	1/1/2016	1/1/2017	1/1/2018	1/1/2019...
21	1/2/2016	1/2/2017	1/2/2018	1/2/2019
40	1/3/2016	1/3/2017	1/3/2018	1/3/2019
59...	1/4/2016...	1/4/2017...	1/4/2018...	1/4/2019...

<tbody>
</tbody>

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*")