+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting Formula for Dates

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Conditional Formatting Formula for Dates

    Hello, I would be grateful for some help with the following scenario in which the conditional formatting will be in column Q:

    If cell S4 is blank then take the date in cell Q4 and subtract it from TODAY making sure that the equation takes into account weekdays and HOLIDAYS (there is a worksheet for the holidays).
    If the result is 5 days or greater and 10 days or less then format in yellow.
    If the result is 11 days or greater then format in red.

    Column Q is the "Date Received" and column S is the "Date Completed"

    If column S contains "N/A" then no formatting - not sure if this needs to be considered.

    Thank you

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Conditional Formatting Formula for Dates

    Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Conditional Formatting Formula for Dates

    I am updating my column info as follows based on the ATTACHED Sample spreadsheet:

    If cell G2 is blank then take the date in cell E2 and subtract it from TODAY making sure that the equation takes into account weekdays and HOLIDAYS (there is a worksheet for the holidays).

    If the result is 5 days or greater and 10 days or less then format in yellow.
    If the result is 11 days or greater then format in red.

    Column E is the "Date Received" and column G is the "Date Complete"

    If column E contains "N/A" or "HOLD" those should be ignored by the formatting.

    If column G contains "N/A" then those should be ignored by the formatting.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Conditional Formatting Formula for Dates

    Try

    for YELLOW

    =AND(G2="",ISNUMBER(E2),NETWORKDAYS.INTL(E2,TODAY(),1,Holidays)>=5,NETWORKDAYS.INTL(E2,TODAY(),1,Holidays)<=10)

    for RED

    AND(G2="",ISNUMBER(E2),NETWORKDAYS.INTL(E2,TODAY(),1,Holidays)>=11)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Conditional Formatting Formula for Dates

    This worked! Thank you so much!

+ 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: 2
    Last Post: 10-03-2017, 06:28 AM
  2. Conditional Formatting with IF-THEN formula using DATES
    By GottaQ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2015, 01:52 PM
  3. [SOLVED] Conditional Formatting or Formula for Repeating Dates
    By long8484 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2014, 02:28 PM
  4. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  5. [SOLVED] formula for conditional formatting (count of dates)
    By joeldlow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 05:04 AM
  6. Formula or conditional formatting for dates
    By Phillipengela in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2013, 02:11 PM
  7. Replies: 8
    Last Post: 03-05-2005, 08:06 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