Conditional Formatting which includes Business Days

Zedstar

New Member
Joined
Jun 5, 2008
Messages
5
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Apr 2, 2009
Messages
175

ADVERTISEMENT

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

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

Zedstar

New Member
Joined
Jun 5, 2008
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,190
Members
414,513
Latest member
junbuggle

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