Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP

You should be able to do that with conditional formatting. Set the condition
to read 'cell value is less than' and the value to compare to is
=TODAY()-$A$1000, with the value in $A$1000 set to the number of days allowed
between retraining less the number of days advance notice desired. You could
set it up so that you've got multiple conditions with different periods to
create shades showing greater urgency, but be sure that you test for the
'most urgent' first.

"Bill Foley" wrote:

Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP

Thanks, bpeltzer! I was hoping to figure out exactly one month from that
day, but this works just fine. Close enough for government work! HA!

I appreciate it!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP
"bpeltzer" wrote in message
...
You should be able to do that with conditional formatting. Set the
condition
to read 'cell value is less than' and the value to compare to is
=TODAY()-$A$1000, with the value in $A$1000 set to the number of days
allowed
between retraining less the number of days advance notice desired. You
could
set it up so that you've got multiple conditions with different periods to
create shades showing greater urgency, but be sure that you test for the
'most urgent' first.

"Bill Foley" wrote:

Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12
months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for
dates
that fall within one month of these periodicities based on the
computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are
more
than 11 months from today's date (A1000 minus 1), they are within one
month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP

Bill,

You should be able to do it without VBA .

When you say periodicity, will you enter a date like 1st Aug 2000, and need
it coloured in Jul 2001, Jul 2002, Jul 2003, etc., or is it a once-ff?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bill Foley" wrote in message
...
Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for
dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are
more
than 11 months from today's date (A1000 minus 1), they are within one
month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP

Actually bpeltzer sort of gave me a solution, but let me explain further in
case of another way.

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
past that date, it still needs to be shaded until a new date is entered when
re-training is done. Since there any number of rows and dates, I need it to
look at each cell in the range and shade it if it is older than 11 months
from today's date.

The kicker (that I have not addressed from the previous solution) is I also
have several cells in a range from A1 - A500 that do not have dates in them
since these folks did not ever get trained on that topic. When I run the
"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to do
a conditional formula to only run a formula if a cell has a value in it, but
wasn't sure if that could be done with conditional formatting also.

THANKS!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP
"Bob Phillips" wrote in message
...
Bill,

You should be able to do it without VBA .

When you say periodicity, will you enter a date like 1st Aug 2000, and
need
it coloured in Jul 2001, Jul 2002, Jul 2003, etc., or is it a once-ff?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bill Foley" wrote in message
...
Hey gang,

I was wondering if anyone had a solution for this EXCEL query:

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12
months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for
dates
that fall within one month of these periodicities based on the
computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are
more
than 11 months from today's date (A1000 minus 1), they are within one
month
of needing to be re-trained and I want the cell shading to be a light
yellow.

Can this be done using Conditional Formatting without VBA or do I need
something to be run based on an AutoOpen macro?

TIA!

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP

"Bill Foley" wrote in message
...

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be
shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is already
past that date, it still needs to be shaded until a new date is entered
when
re-training is done. Since there any number of rows and dates, I need it
to
look at each cell in the range and shade it if it is older than 11 months
from today's date.

My reading of the periodicity made me wonder if you would put a date in, and
then you wanted it highlighted every said period after that date. The fact
that you are want it to saty shaded until it expires and will then update
the date makes it significantly easier.

You can use the technique given, but still use a number of months as you
originally asked with a formula of

=DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))

"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how to
do
a conditional formula to only run a formula if a cell has a value in it,
but
wasn't sure if that could be done with conditional formatting also.

This can be handled, albeit with a more complex formula. To do this, and
apply to all cells and all sheets

On the first sheet, select all the cells that will have dates on one
worksheet (assuming starting at A2)
Group from that sheet to the last (hold shift and click the final sheet tab)
FormatConditional Formatting
Change conditions 1 to Formula Is
Add this formula
=AND(A2<"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
Add the format
OK

Sure enough worked as well. THANKS!

One last thing - Any idea how to skip blank cells in a range (or have an IF
statement that only does the conditional formatting if the cell has a date
in it)?

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP
"Bob Phillips" wrote in message
...

"Bill Foley" wrote in message
...

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be
shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is
already
past that date, it still needs to be shaded until a new date is entered
when
re-training is done. Since there any number of rows and dates, I need
it
to
look at each cell in the range and shade it if it is older than 11
months
from today's date.

My reading of the periodicity made me wonder if you would put a date in,
and
then you wanted it highlighted every said period after that date. The fact
that you are want it to saty shaded until it expires and will then update
the date makes it significantly easier.

You can use the technique given, but still use a number of months as you
originally asked with a formula of

=DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))

"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how
to
do
a conditional formula to only run a formula if a cell has a value in it,
but
wasn't sure if that could be done with conditional formatting also.

This can be handled, albeit with a more complex formula. To do this, and
apply to all cells and all sheets

On the first sheet, select all the cells that will have dates on one
worksheet (assuming starting at A2)
Group from that sheet to the last (hold shift and click the final sheet
tab)
FormatConditional Formatting
Change conditions 1 to Formula Is
Add this formula
=AND(A2<"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
Add the format
OK

Hi Bill

You could use an AND() statement wrapped around Bob's solution.
=AND(NOT(ISBLANK(A$1000)),DATE(YEAR(TODAY()),MONTH (TODAY())-A$1000+1,DAY(TODAY())))

Regards

Roger Govier


Bill Foley wrote:
Sure enough worked as well. THANKS!

One last thing - Any idea how to skip blank cells in a range (or have an IF
statement that only does the conditional formatting if the cell has a date
in it)?

Bill,

That last formula I gave you, with the way to implement it, should do just
that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bill Foley" wrote in message
...
Sure enough worked as well. THANKS!

One last thing - Any idea how to skip blank cells in a range (or have an
IF
statement that only does the conditional formatting if the cell has a date
in it)?

--
Bill Foley
www.pttinc.com
Microsoft PowerPoint MVP
"Bob Phillips" wrote in message
...

"Bill Foley" wrote in message
...

Let's say A15 has a date of 10/1/2004 (Oct 1, 2004). I need it to be
shaded
when it is within 1 month of 10/1/2005 (Oct 1, 2005). Since it is
already
past that date, it still needs to be shaded until a new date is
entered
when
re-training is done. Since there any number of rows and dates, I need
it
to
look at each cell in the range and shade it if it is older than 11
months
from today's date.

My reading of the periodicity made me wonder if you would put a date in,
and
then you wanted it highlighted every said period after that date. The
fact
that you are want it to saty shaded until it expires and will then
update
the date makes it significantly easier.

You can use the technique given, but still use a number of months as you
originally asked with a formula of

=DATE(YEAR(TODAY()),MONTH(TODAY())-A$1000+1,DAY(TODAY()))

"=TODAY()-$A$1000" condition, blank cells are also shaded. I know how
to
do
a conditional formula to only run a formula if a cell has a value in
it,
but
wasn't sure if that could be done with conditional formatting also.

This can be handled, albeit with a more complex formula. To do this, and
apply to all cells and all sheets

On the first sheet, select all the cells that will have dates on one
worksheet (assuming starting at A2)
Group from that sheet to the last (hold shift and click the final sheet
tab)
FormatConditional Formatting
Change conditions 1 to Formula Is
Add this formula
=AND(A2<"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-A$10+1,DAY(TODAY())))
Add the format
OK