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$814
--
Regards
Mike

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

Oops, forgot you can't use ATP functions in CF. Try this instead

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

--
HTH

Bob

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

"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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
FormatConditional Formatting
Formula Is: =$AI$8WorkDate
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.


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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
--
Regards
Mike


"T. Valko" wrote:

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
FormatConditional Formatting
Formula Is: =$AI$8WorkDate
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.


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

Use:

WorkDate = WORKDAY(TODAY(),-14)

And:

=$AI$8<WorkDate

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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
--
Regards
Mike


"T. Valko" wrote:

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
FormatConditional Formatting
Formula Is: =$AI$8WorkDate
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.


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

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
--
Regards
Mike


"T. Valko" wrote:

Use:

WorkDate = WORKDAY(TODAY(),-14)

And:

=$AI$8<WorkDate

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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
--
Regards
Mike


"T. Valko" wrote:

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
FormatConditional Formatting
Formula Is: =$AI$8WorkDate
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.


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike

Try this:

=AND($AI$8<"",$AI$8<WorkDate)

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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
--
Regards
Mike


"T. Valko" wrote:

Use:

WorkDate = WORKDAY(TODAY(),-14)

And:

=$AI$8<WorkDate

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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
--
Regards
Mike


"T. Valko" wrote:

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
FormatConditional Formatting
Formula Is: =$AI$8WorkDate
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.


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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

--
Regards
Mike


"T. Valko" wrote:

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 InsertNameDefine
Name: WorkDate
Refers to: =WORKDAY(TODAY(),14)
OK

Then, use this formula in the conditional formatting:

=$AI$8WorkDate


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
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" wrote:

=$AI$8WORKDAY(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)

"Mike" wrote in message
...
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$814
--
Regards
Mike