+ Reply to Thread
Results 1 to 11 of 11

Conditional formatting date range

  1. #1
    Registered User
    Join Date
    01-28-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Conditional formatting date range

    I am trying to track dates on my spredsheet but am having great difficulty figuring out the formatting for the date ranges. I need:

    all dates before today = black fill/white font
    today-next 3 months = red fill/yellow font
    next 4 months-next 6 months = yellow fill/black font
    next 7 months + = no fill/black font

    I am using Excel 2007, and I have very little excel experience so please dumb down the answer. Thanks for any help you all can offer!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,494

    Re: Conditional formatting date range

    What cells are used for the dates?

    Pete

  3. #3
    Registered User
    Join Date
    01-28-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional formatting date range

    I currently have all the dates in collumn H.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,494

    Re: Conditional formatting date range

    Okay, assume you have a header row and that your data starts in H2, then highlight all the dates from H2 downwards, then click on Conditional Formatting | New Rule and choose "Use a Formula ...". Put this formula in the box:

    =AND(H2>0,H2<TODAY())

    then click the Format button and on the Font tab, Color drop-down choose White, then on the Fill tab choose black, then OK your way out.

    You can add the other conditions in the same way, i.e. highlight the data, click CF, click New Rule, then Use a formula ..., and you can use these formulae and settings in turn:

    =AND(H2>=TODAY(),H2<=TODAY()+91) with a format of Red fill and Yellow foreground, then

    =AND(H2>TODAY()+91,H2<=TODAY()+182) with a format of Yellow fill and Black foreground.

    There is no need to set a format for the 4th condition that you listed.

    Note that I've simplified it to 91 days per quarter.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    01-28-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional formatting date range

    Thank you Pete for your quick response, however for some reason it isnt working right for me. The dates way in the future, such as Nov 2020 are black fill/white font even though they shouldnt have any formatting since it is more than 6 months in the future. Also past dates such as Jan 2006 have no formatting even though they should be black fill/white font. Please help, I feel like this should be any easy fix that I am missing due to my inexperience. Thanks again for your help!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,494

    Re: Conditional formatting date range

    Perhaps you hadn't highlighted the correct range. Can you attach the workbook, and I'll set it up for you? The FAQ describes how to attach a file to your post.

    Pete

  7. #7
    Registered User
    Join Date
    01-28-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional formatting date range

    blank template..xlsx

    Pete,
    Im not sure I attached the workbook corrrectly, so if it doesnt work, please let me know. Also I removed all information from the book except the dates so you could see the ranges I work with and test if it works.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,318

    Re: Conditional formatting date range

    try these...
    =H2<TODAY()
    =AND(H2>=TODAY(),H2<=TODAY()+90)
    =AND(H2>TODAY()+90,H2<=TODAY()+182)
    =AND(H2<=TODAY()+182,H2<=TODAY()+365)

    format each 1 as required
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,494

    Re: Conditional formatting date range

    I've applied the CF that I recommended earlier to the attached file. You can only see two of them in operation, but if you change the date in cell H31 to 1st June 2013 then you will also see the effect on a date of between 3 and 6 months in the future.

    Hope this helps.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-28-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional formatting date range

    Thank you so much both Pete and FDibbins! I am able to finally start working on my spreadsheet thanks to Pete, and I saved the formatting FDibbins wrote in case I lose the pre written rules Pete input for me. You guys are amesome!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,318

    Re: Conditional formatting date range

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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