Results 1 to 7 of 7

Thread: Conditional Formatting For Due Dates

  1. #1
    Neophyte patauld's Avatar
    Join Date
    Nov 2021
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Conditional Formatting For Due Dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I am using an excel sheet to track casework and using conditional formatting to show when tasks are overdue (they have missed their 5 day deadline). I am using the following formula:

    =AND(NETWORKDAYS(D3,TODAY())>5,D3<>"")

    I would really like to add in a custom list of holidays so that they aren't counted but I have tried creating names ranges and adding them into the formula (see below) but it doesn't register the dates:

    =AND(NETWORKDAYS(D3,TODAY,Holidays33)>5,D3<>"")

    If anyone could help explain what the best method for getting a list of holidays built in, that would be super helpful!

  2. #2
    Neophyte HockeyDad's Avatar
    Join Date
    Nov 2021
    Posts
    1
    Articles
    0
    Excel Version
    Microsoft 365
    Hi,

    Assuming that you have a named range for your holidays called Holidays33. Just make sure that the range only includes the dates and not the column title and that the dates are formatted the same way as the rest of the worksheet and it should work. Also you could use a table instead of a named range which does allow a title for the column and you can add to the table without having to change the range name or range in your formula. Tested it with =AND(NETWORKDAYS(C6,TODAY(),Holidays33)>5,C6<>"") and a table called Holidays33 and the values updated as I added dates to the table.

    Today Task Date Task Late? Days Actual
    Days
    Holidays
    11/5/2021 Thursday, November 4, 2021 Test 1 FALSE 2 1 9/2/2021
    11/5/2021 Monday, October 4, 2021 Test 2 TRUE 23 32 9/15/2021
    11/5/2021 Friday, October 29, 2021 Test 3 FALSE 5 7 9/30/2021
    10/6/2021
    11/1/2021

  3. #3
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    55
    Articles
    0
    Excel Version
    2016 32bit
    If you have all the holiday dates in range named Holidays33 then maybe it's only a typo, but, try the formula like this (note Today with the brackets):

    =AND(NETWORKDAYS(D3,TODAY(),Holidays33)>5,D3<>"")

  4. #4
    Neophyte patauld's Avatar
    Join Date
    Nov 2021
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Thanks a lot for the suggestion there! I have tried your formula as well but it still doesn't register the holidays I entered into the named range unfortunately. I should have confirmed in my original post that my named range is on a separate sheet from where I have the conditional formatting applied, is this potentially causing the issue? My rows move to different sheets depending on their status so I wanted to avoid having to maintain multiple named ranges

  5. #5
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    55
    Articles
    0
    Excel Version
    2016 32bit
    No problem with Named range in other sheet, see attached example.
    Attached Files Attached Files

  6. #6
    Neophyte patauld's Avatar
    Join Date
    Nov 2021
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Thanks so much for your help, I tried this again and it works now. I believe the actual issue was being caused by some previously created conditional formatting rules that were overriding the intended results. Thanks again for your help, I really appreciate it!

  7. #7
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    55
    Articles
    0
    Excel Version
    2016 32bit
    Glad having been of some help .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •