Conditional Formatting which includes Business Days

[Zedstar]

Hi All,

I have a very quick question to ask. I'm adding a simple formula to conditional formatting which will warn me 2 days in advance when I should, say, call someone from a huge list of people and dates I am meant to call them.

the formula I use is: =H2-2<=TODAY()

The problem is that this does not take into account business days. For example if I need to call someone on Monday, the formula would warn me on Saturday, when in fact I would want it to warn me on Thursday which is 2 business days before Monday (i.e excluding weekends).

I imagine this is easy so sorry if this appears like a dumb question.

Thanks a lot

 

[Joe4]

There are a few functions that are part of the Analysis ToolPak that comes along with Excel that deal with business days, they are called WORKDAY and NETWORKDAYS. Take a lot at Excel's built-in help for details and examples of how to use these functions.

 

[Mike Blackman]

Hi,

Maybe;

=(NETWORKDAYS(H2,TODAY())+3)>=0

 

[Richard Schollar]

Hi

You need to use one of the Analysis Toolpak functions for this (WORKDAY) but if you are using xl2003 or below you can't use an ATP function in CF. So what you would have to do is to use an adjacent column to hold your formula and then refer to the formula result in this cell to determine whether you conditionally format your data cell:

Assume:
1. A2:A10 is the range of dates you want to conditionally format
2. then in B2 enter the following formula and copy down to B10

=WORKDAY(A2,-2)<=TODAY()

3. Select A2:A10 and go Format>Consitional Formatting. Change to Formula Is and use the formula:

=B2

and hit the Format button and format as required.

 

[mubashiraziz]

Try it and don't think its a dumb question ....

=IF(TEXT(H2,"DDD")="Mon",H2-3<=TODAY(),H2-2<=TODAY())

 

[Zedstar]

Thank you all for helping out but for some off reason none of these work and I followed each instruction to the letter. I use excel 2003 which may be why its not working - I can;t be sure - but thanks for the advice anyway

 

[Richard Schollar]

Do you get a #NAME? error in which case check that you have the Analysis Toolpak installed? From the main Excel interface by going Tools>Add-Ins and chec that it has a check mark against it.

 

[Zedstar]

Thanks Richard for the Tip - it now works - you're my hero.