+ Reply to Thread
Results 1 to 5 of 5

Conditional Format on blank cell to display inactivity based on today, dates in sheet

  1. #1
    Registered User
    Join Date
    09-18-2014
    Location
    Philadelphia, pA
    MS-Off Ver
    Office 2010
    Posts
    9

    Conditional Format on blank cell to display inactivity based on today, dates in sheet

    I've been working at this for a while and can't seem to figure out the formula.

    I'm monitoring the status of projects as they move from stage to stage. There are 5 stages in every project which are always the same. What I would like to accomplish is show that a project has been stuck at a certain stage for longer 15 days by highlighting the cell that otherwise would be completed, had it been completed on time (this cell would be blank).

    I am pulling this data from a program called Salesforce and updating my workbook daily, usually several times a day. So this data is changing as there are anywhere from 80-100 projects in progress at any given time, all of which are at various stages in the project.

    I attached an example. The first two steps have dates signifying that the stages have been completed. It has been over 15 days since the most recent stage was completed. The stage that should have been completed by now 'Design Approved' - I want it to be shaded red to alert me of inactivity. I'm assuming that the TODAY function will need to be used considering the data is going to be updated throughout the day.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Conditional Format on blank cell to display inactivity based on today, dates in sheet

    If I understand your requirements correctly, you need a formula that compares 15 days ago to the maximum date in the other cells. Hopefully the attached file will do what you want, using the formula
    Please Login or Register  to view this content.
    as the condition
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-18-2014
    Location
    Philadelphia, pA
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: Conditional Format on blank cell to display inactivity based on today, dates in sheet

    Yes we would be comparing dates in this case, but I don't know if that formula is what I was looking for, but I think it's close. I'm pretty lost as you can tell. I attached an actual example with dates.

    So for the sake of example, Column G is called Site Approved. I want any of the cells in Column G that are blank to be conditionally formatted to be shaded red in the event that it has been over 15 days since the date in Column E (skin completed date).

    I tried =And($G$18="",TODAY()-$E$18>14)

    The cell turns red, but then when I try to format the rest of the cells, they all turn red which shouldn't be the case.

    So my goal is for the cell to turn red if there has been no activity in over 2 weeks since a previous stage in the project was completed.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Conditional Format on blank cell to display inactivity based on today, dates in sheet

    OK, I think I've got it now. The condition has been applied to columns C-H, with C2 using
    Please Login or Register  to view this content.
    This will colour the cell red if the following three conditions are true:
    • This cell is blank
    • The cell to the left is greater than zero (i.e., not blank)
    • The date in the cell to the left is more than 14 days ago
    Your formula just needed the addition of checking whether the current cell is blank, and also remove the absolute ($) references.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-18-2014
    Location
    Philadelphia, pA
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: Conditional Format on blank cell to display inactivity based on today, dates in sheet

    Thank you!

    Never would have gotten this. I asked another friend about it as well. He came back with a two part formula that is working too. Your process is a little easier but just as an FYI, here is what he offered:

    Use a hidden flag column that establishes when it has been 2 weeks since most recent activity, then use conditional formatting to highlight the next step in the process.

    Flag formula would be: =IF(DATEDIF(HLOOKUP(MAX(I2:O2),I2:O2,1,FALSE),TODAY(),"d")>14,"X","")

    Conditional formatting: =AND(M2>1,N2="",$P2="x")


    Thank you 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: 8
    Last Post: 05-07-2014, 04:57 AM
  2. Conditional Format Cell based on Dates in Other Cells
    By kortekn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 11:50 AM
  3. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  4. Conditional format between dates based on today()
    By StevieNix in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-27-2012, 06:06 PM
  5. how do you conditional format based upon today's date?
    By valoriegill in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2006, 07:50 PM

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