+ Reply to Thread
Results 1 to 4 of 4

Dates and conditional formatting

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Mac 16.16.7
    Posts
    5

    Dates and conditional formatting

    So I have this report with 3 columns of dates. Created, Complete, and Due. I need to:

    1. Highlight a cell in column D (Completed) if that date is more than 10 working days more than the date in column C (Created)

    And

    2. Highlight a cell in column D (Completed) if that date is after the date in Column E (Due)

    If both above rules apply I'd like the color of the highlighted cell to be a 3rd color, different from whatever color I pick to highlight rule 1 or rule 2.

    Screen Shot 2019-10-02 at 5.23.04 PM.png

    Any help would be greatly appreciated. Happy to share the actual excel file. Thanks

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Dates and conditional formatting

    Next time, please include an Excel sheet, not a non-editable picture of one:

    =OR(NETWORKDAYS(C2,D2)>10,E2>D2)

    and

    =AND(NETWORKDAYS(C2,D2)>10,E2>D2)
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Mac 16.16.7
    Posts
    5

    Re: Dates and conditional formatting

    Glenn thanks for your help. I placed your formulas in columns F and G. I'm obviously not doing something right though. See attached.
    Attached Files Attached Files

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Dates and conditional formatting

    1. I made a mistake in the formula.

    2. You didn't apply the formula in a conditional format. With any cell in column D selected:
    Home/Conditional Formatting/Manage rules
    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. [SOLVED] Multi-Conditional Conditional Formatting and Dates
    By Mousiefuzz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2015, 04:09 AM
  2. Conditional formatting using dates and using dates without years
    By dcef79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 05:35 AM
  3. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  4. [SOLVED] Conditional Formatting Due dates and Completed Dates
    By shansen79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-30-2014, 01:33 PM
  5. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  6. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  7. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 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