Use Conditional Formatting to Hide Future Weeks

I am building a spreadsheet to track the weekly standings in a league. I am hoping I can have boxes next to each division's standings that tracks which weeks (if any) are missing results. I was thinking to use conditional formatting to change the font color of future weeks to white (so they appear empty when I post the spreadsheet) however, I can't figure out how to make it work. I've attached the spreadsheet. On the 'standings' tab H3-S3 is where I will track the weeks for the team "THE*BOYS". If they have a score on the 'scores' tab for week 1 H3 will be blank. However, to avoid confusion I don't want weeks 2-12 to have anything until that week has past. I was thinking of adding the end date for each week somewhere to the right (so it won't print) to give excel something to compare 'today()' with, but I can't figure out how to make it work right.

On your 'Standings' sheet, enter numbers 1-12 again in columns U-AF, on row 2. Above each week number, enter the end date of that week.**

Select H2:S6 (the range you want to Conditionally Format) and click 'Conditional Format' then 'New Rule' then 'Use a formula to determine which cells to format'. Enter this in the formula box:

Formula:

Please Login or Register  to view this content.

This will look one row up and 13 columns to the right and compare that date to today's date. Choose white font colour and that should do what you want.
(Obviously you can move the dates anywhere you want on the sheet - just adjust the row offset and column offset appropriately.)


** The week numbers aren't actually needed, but it makes it a lot easier to check you have the right dates .

Thank you very much. It works exactly as I had hoped. I even modified it a bit to hid the week numbers that have not happened yet.

You're welcome - and thanks for the rep.