+ Reply to Thread
Results 1 to 4 of 4

Use Conditional Formatting to Hide Future Weeks

  1. #1
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    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.
    Attached Files Attached Files
    "Laugh? I thought I'd die!"

    Jimbo?

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Use Conditional Formatting to Hide Future Weeks

    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: copy to clipboard
    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 .
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Re: Use Conditional Formatting to Hide Future Weeks

    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.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Use Conditional Formatting to Hide Future Weeks

    You're welcome - and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting based on calculated future dates (month only, ignore day and year)
    By Nitsirk82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-27-2014, 06:42 PM
  2. Hide Future Rows Containing Future Dates.
    By Raj_121 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2013, 07:14 AM
  3. Excel 2003 conditional formatting on future deadlines
    By Racha3146 in forum Excel General
    Replies: 3
    Last Post: 10-22-2012, 11:07 AM
  4. Replies: 2
    Last Post: 05-29-2012, 02:26 PM
  5. Hide rows and conditional formatting
    By olga6542 in forum Excel General
    Replies: 8
    Last Post: 05-02-2011, 06:29 PM
  6. conditional formatting for future dates
    By just_tonight in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-11-2011, 10:04 AM
  7. Excel 2007 : Hide Conditional Formatting
    By mcmuney in forum Excel General
    Replies: 0
    Last Post: 09-15-2010, 06:25 PM
  8. conditional formatting weeks
    By minkus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2008, 06:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1