+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Time Limit / Due Date

  1. #1
    Registered User
    Join Date
    08-22-2020
    Location
    Uk
    MS-Off Ver
    Excel 2016
    Posts
    17

    Conditional Formatting Time Limit / Due Date

    Hi

    I would like to create conditional formatting rules for a matrix i am completing.

    The conditional formatting would require that:

    if a due date falls 28 days or less from todays date - Green Highlight

    If a due date falls 9 days or less from todays date - Orange Highlight

    If a due date falls On todays date or todays date has passed - Red Highlight.

    I also then need a rule that will ignore all three of these rules and change the cell to a different colour if a completion date is entered in a different cell.

    I hope someone can help me with this.

    Thank you in advance

  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
    44,083

    Re: Conditional Formatting Time Limit / Due Date

    Post a sample workbook. See the yellow banner.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-22-2020
    Location
    Uk
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Conditional Formatting Time Limit / Due Date

    Screenshot (2).png

    Im really sorry but for some reason the attachment link wont let me post a sample workbook so the best i can do is attach a screenshot

    I will try to explain best i can.

    One Row for due dates

    One row for actual completion dates

    The completion row is already complete and doesnt need formatting.

    The blue box is the start of the matrix and doesn't need work also. I have programmed the form that the next due date automatically populates 28 days after the last completion date. As you can see here D3 is 28 days after C4.

    When a completion date is entered in D4, E3 will populate etc.

    That is when i need the colour formatting rules to begin in the due dates.

    Sorry i cannot post a workbook as the attachment button does not do anything

  4. #4
    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
    44,083

    Re: Conditional Formatting Time Limit / Due Date

    No, the attachment icon doesn't work. Follow the instructions in the yellow banner at the top of every screen.

  5. #5
    Registered User
    Join Date
    08-22-2020
    Location
    Uk
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Conditional Formatting Time Limit / Due Date

    Apologies I was looking in the wrong place completely. I have attached it now
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Conditional Formatting Time Limit / Due Date

    Hello Wilkinson921 and Welcome to Excel Forum.
    Perhaps this will help:
    The rule for blue (completion date is entered) is: =ISNUMBER(C4)
    The rule for red is: =C3<=TODAY()
    The rule for orange is: =C3-9<=TODAY()
    The rule for green is: =C3-28<=TODAY()
    Note that the blue rule needs to be first to have priority.
    Note that all rules are applied to C3:I3
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    08-22-2020
    Location
    Uk
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Conditional Formatting Time Limit / Due Date

    Brilliant thank you so much that works perfectly!!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Conditional Formatting Time Limit / Due Date

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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 based on time of day, regardless of date
    By YeahWhoDuh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2019, 04:08 PM
  2. [SOLVED] Conditional formatting based on date and time
    By Jalex5134 in forum Excel General
    Replies: 6
    Last Post: 10-26-2017, 05:55 PM
  3. [SOLVED] Conditional Formatting with Date/Time with Date/Time of another cell
    By jnepsa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-20-2017, 10:48 AM
  4. Conditional Formatting Formula - Date & Time for intervals
    By jamesfxd in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2015, 12:23 AM
  5. Replies: 2
    Last Post: 12-27-2011, 04:25 PM
  6. To get around Conditional formatting limit
    By dantonic in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-06-2011, 01:37 AM
  7. [Solved] Conditional Formatting based on date and time
    By HackerJL in forum Excel General
    Replies: 5
    Last Post: 06-08-2011, 06:58 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