Conditional Formatting, Complex If Command

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

Dear All,

I am trying to do the following:

if today()-17>=B5 or today()-18>=B5 colour B6 yellow, but only if B6 is
Blank, if B6 is not blank, colour grey
OR
if (B5+20)<=today(), colour B6 red, but only if B6 is blank, if B6 has
anything in it, colour grey

This is a bit on the complex side for me, could manage it in access, but
excel is all new to me, any help greatly appreciated

Kirstie

ps - many thanks to Andy B who helped with the first bit of this.
 
Kristie,

I'll just make sure i've got it right

If the date in B5 is 17 to 19 days old colour B6 red
If the date in B5 is 20 or more days old colour B6 yellow

Your third condition isn't really a condition at all. If B6 is blank
you won't see the colour so make it grey as a default.

Select cell B6 and choose Format -> Conditional Formatting

Condition 1:
Formula Is =TODAY()-20 >=$B$5
Set the format to be yellow

Condition 2:
Formula Is =TODAY()-17 >= $B$5
Set the format to be red

Dan E
 
Dan,

Sorry, didn't make myself clear, have tried again,

If the date in B5 is 20 or more days old, colour B6 red
If the date in B5 is 17,18 or 19 days old colour B6 yellow
If B6 has text in it colour it grey.
Also, i would like all these to be counted as WORKING DAYS, not just DAYS.

HELP!!

Kirstie
 
Kirstie,

The NETWORKDAYS function is the one best suited to your problem,
however, it will NOT function in conditional formatting (it's part of the
analysis toolpak add-in)

Install the analysis toolpak
Tools -> Add-ins -> check the analysis toolpak (you might need your
office/excel disc)

In another cell on the worksheet enter the formula (example C5)
=NETWORKDAYS(B5,TODAY(),XYZ)
XYZ is a list of holidays in the form:
{"December 25, 2003","January 1, 2004"}

Then for your conditional formatting (Again default grey)
Condition 1:
Formula Is =$C$5 >= 20
Set the format to be yellow

Condition 2:
Formula Is =$C$5 >= 17
Set the format to be red

Dan E
 
Back
Top