+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting for difference between target date and today's date

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Colchester, England
    MS-Off Ver
    Office 2013
    Posts
    8

    Conditional formatting for difference between target date and today's date

    Good morning all (sorry my first thread has to be probably a fairly straightforward request!)

    I've been asked to create a database for our quality assessment schedule and doing so requires a 'countdown' effect for the target date of the next assessment. I'm using =TODAY() to give today's date in G2 at all times, and I want the target date boxes to be shaded:
    • RED if the target date is within 30 days
    • YELLOW if the target date is between 30-60 days
    • GREEN if the target date is between 60-90 days (maximum possible range)

    I have figured that I can only do "this month" or "next month" on the standard CF options, but I'm sure there must be a pretty simple code/formula for doing what I want to achieve. I've tried messing around with =DAYS and =IF but don't know enough about these formulae to get it right.

    I've attached a dummy worksheet for help with reference.

    Thanks in advance for any pointers!

    Rob
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,369

    Re: Conditional formatting for difference between target date and today's date

    Compare the date to G2-30, G2-60, G2-90 although G2-90 might be redundant.


    Regards, TMS

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Colchester, England
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Conditional formatting for difference between target date and today's date

    Sorry TMS, not with you. Would this be through formula or code?

    G2-90 won't be redundant, I haven't told the spreadsheet that the max range is 90 days as a colleague will be putting all the dates in manually.

  4. #4
    Registered User
    Join Date
    04-04-2014
    Location
    Colchester, England
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Conditional formatting for difference between target date and today's date

    Never mind - just realised a VERY boneheaded mistake that was probably causing me the same problem with everything I tried! Must revise the difference between < and > ...

    For peace of mind, why couldn't I get =IF((D6-G2)>=60,format fill GREEN,format fill YELLOW) to work?

    Thanks again for your help

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,369

    Re: Conditional formatting for difference between target date and today's date

    Thanks for the rep.


    Use ...

    Red:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Amber/Yellow:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Green:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Beyond 90 days, no formatting will be applied

    Regards, TMS

  6. #6
    Registered User
    Join Date
    04-04-2014
    Location
    Colchester, England
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Conditional formatting for difference between target date and today's date

    All applied, working great. Thanks again - and don't mention it.

+ 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. Conditional formatting Red, Amber, Green, based on a target date
    By JimFiggs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2021, 04:47 PM
  2. Difference bewteen Due & despatch date & Sue and today date
    By Enigmatise_1981 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 12:37 PM
  3. Replies: 4
    Last Post: 02-13-2013, 01:18 PM
  4. Replies: 1
    Last Post: 01-24-2013, 04:52 PM
  5. Conditional formatting based on date - not today's!
    By HarveyDickinson in forum Excel General
    Replies: 1
    Last Post: 09-11-2009, 05:46 AM

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