Conditional Formatting For Due Dates

[patauld]

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!

[HockeyDad]

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

[rollis13]

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<>"")

[patauld]

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

[rollis13]

No problem with Named range in other sheet, see attached example.

[patauld]

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!

[rollis13]

Glad having been of some help .