+ Reply to Thread
Results 1 to 7 of 7

Use conditional formatting to highlight dates based on upcoming deadline

  1. #1
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Use conditional formatting to highlight dates based on upcoming deadline

    Hi,

    I'm having a brain freeze!!! I just cannot think through this conditional formatting statement.

    The conditions:
    -- If the current (todays) date is 8 working days after the created date, then highlight in orange
    -- If the current (todays) date is 10 working days after the created date, then highlight in red

    On other words...
    -- The dates that will get highlighted in red are overdue for processing
    -- The dates that become orange are approaching the overdue time
    -- Any dates that are less than 8 working days are NOT highlighted at all

    Please review the attachment for setting up the conditions.
    Many TX for your help
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    Hi,

    Check this

    Punnam
    Attached Files Attached Files

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    As i understand that, first rule will be

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


    and 2nd one.

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    Try this.......

    For Red....
    Please Login or Register  to view this content.
    For Orange
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    Gosh.... Three very different formulas as feedback...
    (I don't know why this little scenario has been such a challenge?! On the surface it seems so logical!)

    Based on the feedback (that I appreciate!), I think I have settled it. Just bouncing this off you guys has given me a moment of clarity and I believe I have the solution...

    Red: =TODAY()-WORKDAY(A2,10)>=10
    Orange: =TODAY()-WORKDAY(A2,8)>=8

    This seems to provide the expected output.
    Much obliged all

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    Formulas are not difficult, IF for anyone that get the logic. That's why i said in my reply:

    ...As i understand that,

  7. #7
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Use conditional formatting to highlight dates based on upcoming deadline

    Absolutely agree Fotis.

    I do a lot of Excel support in the company I work for. I can string together so large nested functions without any hassles (IF I understand the needs and requirements of the requester). If you don't understand the context of the calculation, even simple expressions can feel frustrating to create.

    Cheers again!

+ 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. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  2. Using Conditional Formatting to highlight cells based on approaching dates
    By BrutalExcel in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 06-12-2013, 11:56 AM
  3. [SOLVED] Excel 2010 Conditional Formatting to Highlight input cells based on Dates
    By chrisb84 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2013, 09:39 AM
  4. Conditioning Formatting based on deadline dates
    By celeliza in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2013, 03:08 AM
  5. Replies: 5
    Last Post: 09-24-2012, 05:14 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