+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting for due dates and completion percentages.

  1. #1
    Registered User
    Join Date
    06-15-2018
    Location
    Ldn
    MS-Off Ver
    2016
    Posts
    10

    Conditional formatting for due dates and completion percentages.

    Hi All,

    I would like to format a project tracker that I have set up on excel.

    I have a Deadline/Due date column which gives the date the project needs to be completed by (Column J) and I have a percentage column of an estimated % of how far along the project is (Column M)

    What I would like my spreadsheet to do is:

    Highlight the date in column J red when the date passes AND the percentage in column M is less than 100%
    Not highlight the date if the percentage in column M is 100% and the deadline date in column J has passed
    Not highlight the date if the percentage in column M less than 100% and the deadline date in column J has not yet passed

    Can anyone help me with this? I have tried everything I can think of and searched numerous posts!

    TIA
    Attached Files Attached Files
    Last edited by chlomo; 06-15-2018 at 10:02 AM.

  2. #2
    Registered User
    Join Date
    06-04-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    54

    Re: Conditional formatting for due dates and completion percentages.

    =AND($M4<1,$J4<=TODAY())

    Apply this formula in conditional formatting in column J4 and then copy paste formatting (ALT+H+V+R ) to other cell

    Please find attached solution file
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-15-2018
    Location
    Ldn
    MS-Off Ver
    2016
    Posts
    10

    Re: Conditional formatting for due dates and completion percentages.

    Thank you!! One more thing, I have applied the above to all cells in that row but the blanks also highlight. How can I stop blanks from highlighting?

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

    Re: Conditional formatting for due dates and completion percentages.

    without looking at the file, maybe this?
    =AND($M4<>"",$M4<1,$J4<=TODAY())
    (assuming M4 is the cell that would be blank - adjust as needed)
    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

  5. #5
    Registered User
    Join Date
    06-15-2018
    Location
    Ldn
    MS-Off Ver
    2016
    Posts
    10

    Re: Conditional formatting for due dates and completion percentages.

    Thank you!!

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

    Re: Conditional formatting for due dates and completion percentages.

    Happy to help

+ 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. [SOLVED] Conditional Formatting with Data Bars - Based on Completion Percentage
    By benwahchang in forum Excel General
    Replies: 9
    Last Post: 11-14-2014, 03:38 PM
  2. Replies: 16
    Last Post: 05-08-2014, 04:24 PM
  3. [SOLVED] Conditional formatting for percentages
    By dr_jon in forum Excel General
    Replies: 1
    Last Post: 08-08-2012, 11:31 AM
  4. Conditional Formatting with percentages
    By tracykay1 in forum Excel General
    Replies: 5
    Last Post: 12-12-2011, 02:17 PM
  5. Conditional formatting - job completion status by colour
    By newE in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-27-2011, 04:19 PM
  6. Replies: 18
    Last Post: 10-23-2009, 11:54 AM
  7. Conditional formatting with percentages
    By Dan27 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2009, 07:13 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