+ Reply to Thread
Results 1 to 4 of 4

Conditional Format Based on Date and Source of Text

  1. #1
    Registered User
    Join Date
    03-27-2018
    Location
    Madison, WI
    MS-Off Ver
    2007
    Posts
    2

    Conditional Format Based on Date and Source of Text

    I'm trying to put together and aggregate Gantt chart to track the progress of several Gantt charts that are running at the same time but all are separate sheets in the same workbook.

    H33 = Actual End Date
    J33 = Timeline End Date

    If there IS a date in the H33 field, I want to populate that with the formula below.
    If there is no date in the H33 field, I want to populate the date that IS definitely in J33
    This part all works fine with the following formula:

    =IF('SheetName'!H33="",'SheetName'!J33,'SheetName'!H33)

    Here's what I'm looking to do next though.
    I want to get conditional formatting to fill the cell green if a date was pulled from H33 (this means job is actually done so it's off our radar)
    If it pulls the date from J33, I would like it to turn Yellow if we are currently within 1 week of that date coming up and red if we are 3 days from that date.

    Does anyone know how to set conditional formatting to do something like this?

    Maybe I conditionally format the cells on the other Gantt charts and inherit whatever their conditions are on the aggregate sheet?

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Conditional Format Based on Date and Source of Text

    Attach a sample workbook with your expected outcomes.

    Go Advanced then Manage Attachments then Upload.

  3. #3
    Registered User
    Join Date
    03-27-2018
    Location
    Madison, WI
    MS-Off Ver
    2007
    Posts
    2

    Re: Conditional Format Based on Date and Source of Text

    Okay, so take a look at this. The Osage Beach line in the Project Progress Sheet...
    I want cell D3 for instance to pull either I33 from the Osage Beach sheet or K33 if I33 is blank.

    When you look at the Osage Beach sheet, row I should fill green anytime a date is filled in.
    Row L, I will want to turn yellow if current date is within 1 week away and red if it's 3 days away or has already passed.

    Then I just want whatever is conditionally filled in on the Osage Beach page to also fill in on the Project Progress page.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,483

    Re: Conditional Format Based on Date and Source of Text

    Hello Mister Blutarsky and Welcome to Excel Forum.
    If I understand correctly, and if I am correct that in the 2007 version you can refer to cells on other sheets when setting up conditional formatting, then perhaps this will help.
    The names of the tasks were copied from the 'Project Progress' sheet and pasted (transpose) into column O of the 'Osage Beach' sheet.
    The rule for green fill is*: =INDEX('Osage Beach'!$I33:$I46,MATCH(D2,'Osage Beach'!$O33:$O46,0))=D3
    The rule for red fill is*: =D3-3<=TODAY()
    The rule for yellow fill is: =D3-7<=TODAY()
    * Check 'Stop if True' for these rules.
    Rows 6:8 on the 'Project Progress' sheet are for placed for demonstration purposes and may be deleted.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 3
    Last Post: 11-23-2013, 06:21 AM
  2. Conditional format a cell based on a due date
    By Taiter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 11:50 PM
  3. conditional format based on date
    By keith6292 in forum Excel General
    Replies: 1
    Last Post: 03-05-2013, 06:07 PM
  4. Replies: 1
    Last Post: 09-30-2012, 03:01 PM
  5. Excel 2007 : conditional format based on date range
    By Rob U Blind in forum Excel General
    Replies: 1
    Last Post: 10-21-2011, 09:41 AM
  6. Conditional Format based on date of another cell
    By marjo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-29-2008, 02:41 PM
  7. [SOLVED] 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
  8. Conditional Format based on date ranges
    By Corey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2006, 12:40 AM

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