+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting Due dates and Completed Dates

  1. #1
    Registered User
    Join Date
    04-18-2014
    Location
    Post Falls, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    5

    Conditional Formatting Due dates and Completed Dates

    I am trying to set up a spread sheet that has Due dates in column A and Completed dates in column B.

    I have conditional formatting to change the due dates to yellow 30 days before they are due and to turn red when they are past due.

    What I am now wanting to do is have the conditional format stop if the due date is after or the same day as the completed date.

    I tried another conditional format =A1>=B1 and formatted it to go back to original cell fill color, but when the B1 is blank A1 does not revert back to the other conditional formatting for the red and yellow colors.

    I think the problem is when the cell B1 is blank it makes the conditional format true and A1 would be greater than B1, but I don't want the blank cell to make the condition true.

    Any help would be greatly appreciated.
    Last edited by shansen79; 04-29-2014 at 02:57 PM.

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

    Re: Conditional Formatting Due dates and Completed Dates

    May be this......

    for yellow......

    Please Login or Register  to view this content.
    and for red.....

    Please Login or Register  to view this content.
    is this what you want?
    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.

  3. #3
    Registered User
    Join Date
    04-18-2014
    Location
    Post Falls, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional Formatting Due dates and Completed Dates

    That did not work, let me try to explain it differently

    Column A has due dates
    Column B has completed dates

    Column A has 3 conditional formats
    1. <NOW() turns the cell red if it is past due
    2. <NOW()-30 turns the cell yellow if 30 days until its due
    3. =IF($A$1>=$B$1, True, False) turns the cell back to original color, lets say white

    This works except I want a blank cell in column B to not make the 3rd condition true and change the color back to the original color.

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

    Re: Conditional Formatting Due dates and Completed Dates

    Try this........

    For yellow

    Please Login or Register  to view this content.
    For red
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-18-2014
    Location
    Post Falls, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional Formatting Due dates and Completed Dates

    Nope not having luck with that either.

  6. #6
    Registered User
    Join Date
    04-18-2014
    Location
    Post Falls, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional Formatting Due dates and Completed Dates

    I have added an example spread sheet.

    You will see column B contains due dates with conditions to turn red when they are past today's date and to turn yellow if they are 30 days away from the due date.

    What I want to happen is when the completed date in column C
    is either the same date as the due date or before the due date for the due date not to turn red, but if the completed date is after the due date to remain red.

    Hopefully this helps clarify what I am trying to do.
    Attached Files Attached Files

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

    Re: Conditional Formatting Due dates and Completed Dates

    See if this works.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-18-2014
    Location
    Post Falls, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional Formatting Due dates and Completed Dates

    That works. Thanks for you help.

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

    Re: Conditional Formatting Due dates and Completed Dates

    Glad to know that. Thanks for the feedback.
    Since you are new to the forum so just to tell you that if that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

+ 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 with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  2. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  3. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 AM
  4. Calculate days between two dates, until job completed.
    By StonyMac in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-06-2013, 04:32 AM
  5. Replies: 4
    Last Post: 07-24-2011, 12:47 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