+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting - Upcoming Dates and Blanks

  1. #1
    Registered User
    Join Date
    10-29-2014
    Location
    Boston, MA
    MS-Off Ver
    Microsoft Office Professional 2013
    Posts
    3

    Unhappy Conditional Formatting - Upcoming Dates and Blanks

    Hello,

    I'm trying to configure an "and" statement within conditional formatting - I think.

    I have a number of upcoming "jobs", all down column A. For each job, there are a number of "tasks", all across line 3. When a task for each job is completed, I simply put an x in the corresponding box.

    My jobs all have a different start dates, and as of right now I have the estimated start date in column C. When the upcoming job starts within in 2 weeks, it highlights the cell orange using =AND(C4-TODAY()>=0,C4-TODAY()<=14). When a job has started, it highlights the cell red using =C4<TODAY().

    Is there any way I can double-conditional format, where if a job is coming up within two weeks and some of the tasks for that job have not been completed, it will highlight that cell? Same with once a job has started. Thank you very much.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Conditional Formatting - Upcoming Dates and Blanks

    you can add another AND() with the cells ="" or <>"X"

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    10-29-2014
    Location
    Boston, MA
    MS-Off Ver
    Microsoft Office Professional 2013
    Posts
    3

    Re: Conditional Formatting - Upcoming Dates and Blanks

    I have tried using that but it doesn't work. Maybe I'm not putting it in the correct place? Please advise.
    Attached Files Attached Files
    Last edited by susstu; 10-29-2014 at 03:24 PM.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Conditional Formatting - Upcoming Dates and Blanks

    what do you want to do about the X
    count them ALL and highlight if any tasks are not complete

    can you explain the full rules please

  5. #5
    Registered User
    Join Date
    10-29-2014
    Location
    Boston, MA
    MS-Off Ver
    Microsoft Office Professional 2013
    Posts
    3

    Re: Conditional Formatting - Upcoming Dates and Blanks

    The object is to see what tasks still need to be done on the jobs that are starting in the next two weeks, and what hasn't been done yet on the jobs that have already started.

    I currently have conditional formatting for the start dates to show if they are upcoming within 2 weeks or have started, but I want to highlight the blank cells respective to those dates. I don't want all the blank cells in the whole sheet to be highlighted, just the ones corresponding with the jobs starting within two weeks. Let me know if this is unclear.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Conditional Formatting - Upcoming Dates and Blanks

    so we are onlyinterested in thejobs that have started or are in 2 weeks
    =AND(C4-TODAY()>=0,C4-TODAY()<=14). When a job has started, it highlights the cell red using =C4<TODAY().

    so we could use those rules

    =OR ( AND($C4-TODAY()>=0,$C4-TODAY()<=14) , $C4<TODAY() )

    and ADD
    D4=""

    so

    =AND( OR( AND($C4-TODAY()>=0,$C4-TODAY()<=14) , $C4<TODAY() ) , D4 ="")

    probably can simplify this down - BUT just using your logic so far

    see attached
    Attached Files Attached Files

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: Conditional Formatting - Upcoming Dates and Blanks

    I have simplified to this

    =AND($C4<=TODAY()+14, D4="")

    see attached
    Attached Files Attached Files

+ 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: 6
    Last Post: 06-06-2014, 07:40 AM
  2. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  3. [SOLVED] Due dates formula in conditional formatting that ignores blanks
    By ciapul12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2013, 08:48 AM
  4. Conditional formatting, with dates and blanks
    By jjsweeney in forum Excel General
    Replies: 1
    Last Post: 10-07-2013, 04:40 AM
  5. Replies: 9
    Last Post: 03-26-2012, 02:16 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