Formulas and Conditional Formatting

lisamarie1771

New Member
Joined
Feb 9, 2014
Messages
8
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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
did that work OK then for both criteria for you ?
 

lisamarie1771

New Member
Joined
Feb 9, 2014
Messages
8
Yes, I changed the < or > and chose the color. I also included the company holidays and it seems to be working.

Thanks
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS

Watch MrExcel Video

Forum statistics

Threads
1,122,947
Messages
5,599,012
Members
414,273
Latest member
vikas007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top