Conditional Formatting - workdays only

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all, hope you can help. Excel 2003

I need to use conditional formatting on a cell if the date in this cell is
14 working days > Today(). I'm not sure where to place the (AI1-Workday)
part within the formula bar

=TODAY()-$AI$8>14
 
=$AI$8>WORKDAY(TODAY(),14)

are you sure that you mean 14 working days, not 10?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Bob, thanks for the quick reply back. However, I get a message as soon as
I try to save it 'You may not use references from other worksheets for
conditional fformatting criteria'

Any clues on this?
--
Regards
Mike


Bob Phillips said:
=$AI$8>WORKDAY(TODAY(),14)

are you sure that you mean 14 working days, not 10?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Oops, forgot you can't use ATP functions in CF. Try this instead

=$AI$8>TODAY()+SMALL(IF((WEEKDAY(TODAY()+(ROW(INDIRECT("1:"&ABS(14)*10))),2)<6),ROW(INDIRECT("1:"&ABS(14)*10))),ABS(14))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Mike said:
Hi Bob, thanks for the quick reply back. However, I get a message as soon
as
I try to save it 'You may not use references from other worksheets for
conditional fformatting criteria'

Any clues on this?
Click to expand...
 
The WORKDAY function is part of the analysis ToolPak add-in which is another
file. That's why you get the message.

Try this:

Create a defined named formula.
Goto Insert>Name>Define
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8>WorkDate


--
Biff
Microsoft Excel MVP


Mike said:
Hi Bob, thanks for the quick reply back. However, I get a message as soon
as
I try to save it 'You may not use references from other worksheets for
conditional fformatting criteria'

Any clues on this?
Click to expand...
 
Hi T, it seems so straight forward but I can't get this to work in the
Conditional Formatting - highlight cell in red. I have tried testing it using
a blank spreadsheet with a date >14 days old in cell A1 and created the Name
as you suggested and retryed but no luck. No errors occur which is a bonus.

Would you be able to test it again for me just to make sure

Thanks again for all your help
 
Would you be able to test it again for me just to make sure

Worked ok for me. Here' what I did:

Defined WorkDate as =WORKDAY(TODAY(),14)

Applied the conditional formatting:

Selected cell AI8
Format>Conditional Formatting
Formula Is: =$AI$8>WorkDate
Set the fill color to green (any color will do, I just like green!)

In AI8 I entered 12/31/2008 and it turned green as expected.

=WORKDAY(TODAY(),14) evaluates to 7/21/2008

So, the format is applied when AI8 > 7/21/2008 (based on today's date).

In AI8 I entered 7/20/2008 and the green format was removed.

In AI8 I entered 7/25/2008 and the green format was again applied.
 
Hi T, I just realised what I did wrong. I misinterpretted the formula with
regards to the dates. It's the other way round. I want the cell in AI8 to
format in Red if the date is 14 working days BEHIND(<) Todays date.

I tried modifying the WorkDate name WORKDAY(TODAY(),14) but it does not like
when I use < sign in it. I also used =$AI$8<WorkDate but does not work out.

Can you help me again - I so appreciate your help in me learning Excel
 
Hi Biff, that works great thanks! I've noticed however that when I apply
this formula/formatting to other cells below which do not have yet dates
inserted into them, they automatically show up in Red formatting ( which
suggest the formula is working). However, this causes confusion during
monitoring of the spreadsheet.
Therefore, can I add a IF statement in here to say "If the cell is blank, do
not format the cell in Red but leave unformatted".

Is this possible?, your help to me is most grateful
 
Back
Top