Excel 2003 - Conditional formatting based on date and to clear when data input

Hi guys,

Need your help and been told may need some conditional formatting to go in.

In my spreadsheet column G records the target date for an action plan. Column H records the completed date.

I need to do some conditional formatting to apply the following:

Lets say the target date for the action plan is 20/04/2014. 5 working days before the target date I want the row to turn yellow, 3 working days I want it to turn yellow and when there are 2 working days left until that date I want it to turn red.

At any time if column H is filled in because the action plan has been completed, I do not want any of the conditional formatting to apply and the row to remain clear as don't want it being flagged up. Also want the row to stay clear until the conditional formatting requested applies (sorry if this is obvious but just making sure I'm clear in what I say)

The calculation has to be based on working days. Is this possible?

Thanks

Imran

Make the check for Row H your first Condition. =IsBlank(H2)
Make the check for less than two days your second condition. You need to enable the Analysis Tool Pack Add-in for the function to Work

=Networkdays(G2,Today())<2

For less than 5 the third.

=Networkdays(G2,Today())<5

Check out the help file for the Networkdays function to show you how to add holidays to it. (below)



NETWORKDAYS function
Show All
Hide All
This article describes the formula syntax and usage of the NETWORKDAYS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Tip To calculate whole workdays between two dates by using parameters to indicate which and how many days are weekend days, use the NETWORKDAYS.INTL function.


Syntax
NETWORKDAYS(start_date, end_date, [holidays])The NETWORKDAYS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Start_date Required. A date that represents the start date.
End_date Required. A date that represents the end date.
Holidays Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal 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.
Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
If any argument is not a valid date, NETWORKDAYS returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.



Selecting an example from Help

Press CTRL+C.
In Excel, create a blank workbook or worksheet.
In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.



A B
Date Description
10/01/2008 Start date of project
3/01/2009 End date of project
11/26/2008 Holiday
12/4/2008 Holiday
1/21/2009 Holiday
Formula Description (Result)
=NETWORKDAYS(A2,A3) Number of workdays between the start and end date above (108)
=NETWORKDAYS(A2,A3,A4) Number of workdays between the start and end date above, excluding the first holiday (107)
=NETWORKDAYS(A2,A3,A4:A6) Number of workdays between the start and end date above, excluding every holiday above (105)


Note To convert the range of cells used for holidays in the last example into an array constant, select reference A4:A6 in the formula and then press F9.

Sorry, I don't have 2003 to test it. It works with 2010. Here's an alternative to Networkdays, but it's slightly more complex.

http://www.cpearson.com/excel/betternetworkdays.aspx

Ignore last post as they array formulas.

I can confirm that 2003 dosen't support add in functions with conditional formatting. You could create a couple of hidden helper columns with the workday formulas in and refer to them in your conditional formatting

Book1.xls

Heres an example of it working