Date Due Reminder
 

On my spread sheet I have a cell to indicate testing due dates - Is there a
way to make that cell flash or change colors 60 days prior to the current
date ?

Hi George

to make it change colours you can use Conditional Formatting - select your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is there
a
way to make that cell flash or change colors 60 days prior to the current
date ?

Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting - select your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is there
a
way to make that cell flash or change colors 60 days prior to the current
date ?

let us know how you go

"George" wrote in message
...
Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting - select
your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is
there
a
way to make that cell flash or change colors 60 days prior to the
current
date ?

Julie - I tried the formula and I can't get it to work. When I type in your
steps the entire column the background changes to Red ( Which I selected )
regardless of the date.My column is E5:E44

Thanks - George


"JulieD" wrote:

let us know how you go

"George" wrote in message
...
Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting - select
your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is
there
a
way to make that cell flash or change colors 60 days prior to the
current
date ?

Hi George

okay, select E5:E44
and then ensure that row 5 is the first row that you can see at the top of
your worksheet .. then
choose format / conditional formatting
choose
formula is
type
=AND($E5=TODAY()-60,$E5<=TODAY())
click Format .. .set your formatting
OK twice

does this work better?
if not ... zip up the workbook and email it to me at julied_ng at hcts dot
net dot au;

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
Julie - I tried the formula and I can't get it to work. When I type in
your
steps the entire column the background changes to Red ( Which I selected )
regardless of the date.My column is E5:E44

Thanks - George


"JulieD" wrote:

let us know how you go

"George" wrote in message
...
Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting - select
your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is
there
a
way to make that cell flash or change colors 60 days prior to the
current
date ?

Julie - I got it to work but the background changes color only for the
current month. What I am trying to do is turn the background red 60 days
prior to the current date and include the current month..

Should this formula make that happen ?

Thanks

"JulieD" wrote:

Hi George

okay, select E5:E44
and then ensure that row 5 is the first row that you can see at the top of
your worksheet .. then
choose format / conditional formatting
choose
formula is
type
=AND($E5=TODAY()-60,$E5<=TODAY())
click Format .. .set your formatting
OK twice

does this work better?
if not ... zip up the workbook and email it to me at julied_ng at hcts dot
net dot au;

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
Julie - I tried the formula and I can't get it to work. When I type in
your
steps the entire column the background changes to Red ( Which I selected )
regardless of the date.My column is E5:E44

Thanks - George


"JulieD" wrote:

let us know how you go

"George" wrote in message
...
Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting - select
your
dates - ensure that the first date is at the top (and visible) of your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates - Is
there
a
way to make that cell flash or change colors 60 days prior to the
current
date ?

Hi George

sorry i didn't notice the bit about to the end of the current month ...
this, as far as i know is a bit more difficult (-60 days from to today to
plus 15 is easy), but to get the last day of the month the only way i know
how to do it is to use the EOMONTH function, which is an analysis toolpak
function (tools / addins / analysis toolpak) and these can't be used in
conditional formatting formulas ... so if you really want -60 from today and
to the end of the month, you will need to put the EOMONTH function in a cell
somewhere and reference that in your formula:
in say cell D6
=EOMONTH(NOW(),1-1)
conditional formatting formula now
=AND($E5=TODAY()-60,$E5<=$D$6)

if however, you do want -60 days to plus 15 days use the conditional
formatting formula of
=AND($E5=TODAY()-60,$E5<=TODAY()+15)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
Julie - I got it to work but the background changes color only for the
current month. What I am trying to do is turn the background red 60 days
prior to the current date and include the current month..

Should this formula make that happen ?

Thanks

"JulieD" wrote:

Hi George

okay, select E5:E44
and then ensure that row 5 is the first row that you can see at the top
of
your worksheet .. then
choose format / conditional formatting
choose
formula is
type
=AND($E5=TODAY()-60,$E5<=TODAY())
click Format .. .set your formatting
OK twice

does this work better?
if not ... zip up the workbook and email it to me at julied_ng at hcts
dot
net dot au;

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
Julie - I tried the formula and I can't get it to work. When I type in
your
steps the entire column the background changes to Red ( Which I
selected )
regardless of the date.My column is E5:E44

Thanks - George


"JulieD" wrote:

let us know how you go

"George" wrote in message
...
Thanks - will give it a try

"JulieD" wrote:

Hi George

to make it change colours you can use Conditional Formatting -
select
your
dates - ensure that the first date is at the top (and visible) of
your
worksheet
choose format /conditional formatting
choose formula is
type
=AND($A2=TODAY()-60,$A2<=TODAY())
where A2 is the first date in your range
click on Format
choose a formatting - background fill colours are under "pattern"
click OK twice

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"George" wrote in message
...
On my spread sheet I have a cell to indicate testing due dates -
Is
there
a
way to make that cell flash or change colors 60 days prior to the
current
date ?