Conditionally Highlight Past-Due Dates Based on WORKDAY Grace Period

[clooysen]

I'd like to conditionally highlight past-due dates based on a 3 "workday" grace period.

Example:
If the date a project is slated to start is 11/16/17 and we want to provide a 3 day grace period that includes the start date, the cell shall highlight on 11/20/17 which will identify it as past-due.

Additionally, I'd like to keep a count of all of these cells that are becoming past-due in a separate cell, so if there's a formula for that, it would help a ton!!

All help is greatly appreciated!!!

[modytrane]

see attached. I have used column D as helper to make things simple.

[modytrane]

You can use similar concept to highlight cells based on today's date. Use TOADY() function.

[clooysen]

OK, I see what you did there. I'm trying to make something a little more "all-inclusive" though. Currently I have a "Start Date" column, this is the one I'm trying to conditionally format. I have it set up right now to highlight all dates that are <= to today's date using this formula: =AND($H1<>"",$H1<=TODAY()). These cells currently highlight pink to show when they are due/past due. I'd like to have the formula modified so that they highlight only when they are 3 business days past the date in the column (ie. if a start date is 11/17/17, it won't highlight until 11/21/17, rather than it highlighting on 11/17/17 as it currently does).

I hope this makes more sense. I've attached a spreadsheet showing what I'm talking about, all cells highlighted in orange are dates that should highlight based on this formatting.

[modytrane]

see attached. Hope that helps.

[clooysen]

Thanks for the help Modytrane. I wound up modifying this formula =COUNTIFS(H:H,"<="&TODAY(),H:H,"<="&WORKDAY(TODAY(),-3,'Holiday List'!B2:B300)) which worked perfectly for the formula portion. I've yet to get your conditional formatting to work for me no matter how hard I try. I'll keep at it tomorrow. Thanks again!

[JeteMc]

Reading the preceding posts I assume that the actual date the project started is what should go into column H. With that in mind the following formula could be used to populate column C:

Please Login or Register  to view this content.

The following formula could be used as a conditional formatting rule, and applied to the range A2:A143 =C2>=3
In the attached copy of the file dates have been placed in some of the cells in column H to demonstrate how the formulas work.
Let us know if you have any questions.