Formulas and Conditional Formatting

[lisamarie1771]

I have a spreadsheet with four columns.

A = Scheduled Pack Date
B = Submission Date (must be 8 days or greater than A)
C = Release Date (must be 3 days or greater than A)
D = Company holidays

I want to set conditionally formatting that determines if B & C are false, after excluding weekends and holidays, then turn the cell red, if true turn green

I only now to turn it red or green without the exclusion of weekends and holidays. I thought using networkdays, but I still can't do it.

Any help would be greatly appreciated.

 

[etaf]

networkdays should work

=NETWORKDAYS(A2,B2)
gives the number then , add in your holiday dates list somewhere an then you can apply the range with all holiday dates in

• An optional range of one or more dates to exclude from the working calendar, such as bank holiday and company holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

and then for conditional formatting use a rule
=NETWORKDAYS(A2,B2) >=8

for that to be 8 days = OR greater

for 2007 or 2010 excel version
Conditional Formatting


Highlight applicable range >>
select the range you want to highlight


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=NETWORKDAYS($A2,$B2) >=8


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

I used $ so the column A and B do not change and then it will highlight any cell on row - you put the apply to

 

[lisamarie1771]

Thank you so much.

 

[etaf]

did that work OK then for both criteria for you ?

 

[lisamarie1771]

Yes, I changed the < or > and chose the color. I also included the company holidays and it seems to be working.

Thanks

 

[etaf]

excellent