CODEWITHSUNDEEP
exceldate
Kadesha Lambert
Kadesha Lambert

Reputation: 1

Condition formatting using workdays only

I am trying to create a formula for conditional formatting that will highlight cells that contain a date that is between the date in the cell and five days prior, excluding weekends and holidays. For example, if the date in the cell is 10/01/2014, It should only be highlighted on the following dates of the week:

9/25, 9/26, 9/29, 9/30, and 10/1.

Any suggestions?

Upvotes: 0

Views: 1957

Answers (1)

chancea
chancea

Reputation: 5968

I am trying to create a formula for conditional formatting that will highlight cells that contain a date that is between the date in the cell and five days prior, excluding weekends and holidays.

This formula will get you all you asked except for the holidays.

In your conditional formatting replace $A$1 with the cell that contains the date, make sure you keep the $'s, and replace C2 with the cell that is the start of your conditional formatting, make sure to NOT use $ (we want this cell to be fluid).

=AND(($A$1-C2)<7,WEEKDAY(C2)<>1,WEEKDAY(C2)<>7)

Apply formatting as desired.

This will first check to see if the date is within at least 7 days of the inputted date, this will work assuming within each 7 day period there will always be 2 weekend days and that we will always exclude exactly 2 days to reach our desired total of 5 days highlighted, then we check to make sure it is not a weekend day using the WEEKDAY() formula. That will return 1 for Sunday and 7 for Saturday so we exclude those values. (In your provided data you do not list the year for the days, note that this formula will default to the current year if no year is provided)

The problem with adding holidays to the picture is that you would have to predefined which holidays you want to exclude (you cannot just say ALL because even that has varying opinion). It would be possible if you predefined each specific day but the formula would be exponentially more complicated since you cannot use the 7 day assumption anymore (since you might be excluding 3 days in a 7 day period the formula would only highlight 4 days)

Anyways this should at least get you a start, if you have any questions leave a comment.

Upvotes: 1

Related Questions