formula to update dates periodically
 

I am creating a spreadsheet and one column will have a date established and
another column will have a follow-up date, which will be three weeks from
established date. Is there a formula to change the follow-up date every three
weeks and can it stop changing when the word "closed" is entered in another
column. The follow-up date already has a conditional format to turn red and
alert user that notices are due. The esatblish date is permanent.

formula to update dates periodically
 

Some thoughts on a possible approach ..

Suppose you have this set-up in cols A and B

EstabDate Status
15-Feb-08 Open
15-Mar-08 Open
etc

You could set up the "21 days" consecutive monitoring in adjacent cols to
the right (eg in col C across), where the 1st col (col C) monitors it for the
1st 21-day period, the 2nd col (col D) monitors it for the 2nd 21-day
stretch, and so on

In C2:
=IF(AND($A2<"",TODAY()=$A2+COLUMNS($A:A)*21,$B2< "Closed"),"Alert"&COLUMNS($A:A),"")
Copy C2 across as far as required / fill down as far as required

You'd get something which looks like this:

EstabDate Status FUpD1 FUpD2
15-Feb-08 Open Alert1 Alert2
15-Mar-08 Open Alert1
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham" wrote:
I am creating a spreadsheet and one column will have a date established and
another column will have a follow-up date, which will be three weeks from
established date. Is there a formula to change the follow-up date every three
weeks and can it stop changing when the word "closed" is entered in another
column. The follow-up date already has a conditional format to turn red and
alert user that notices are due. The esatblish date is permanent.

formula to update dates periodically
 

This would work except that the item maybe opened for an indefinite period
which would result in indefinite columns. The spreadsheet is very large and
it is tediuos searching for overdue items every day. Is there a macros for
this purpose or a different approach to the problem.

"Max" wrote:

Some thoughts on a possible approach ..

Suppose you have this set-up in cols A and B

EstabDate Status
15-Feb-08 Open
15-Mar-08 Open
etc

You could set up the "21 days" consecutive monitoring in adjacent cols to
the right (eg in col C across), where the 1st col (col C) monitors it for the
1st 21-day period, the 2nd col (col D) monitors it for the 2nd 21-day
stretch, and so on

In C2:
=IF(AND($A2<"",TODAY()=$A2+COLUMNS($A:A)*21,$B2< "Closed"),"Alert"&COLUMNS($A:A),"")
Copy C2 across as far as required / fill down as far as required

You'd get something which looks like this:

EstabDate Status FUpD1 FUpD2
15-Feb-08 Open Alert1 Alert2
15-Mar-08 Open Alert1
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham" wrote:
I am creating a spreadsheet and one column will have a date established and
another column will have a follow-up date, which will be three weeks from
established date. Is there a formula to change the follow-up date every three
weeks and can it stop changing when the word "closed" is entered in another
column. The follow-up date already has a conditional format to turn red and
alert user that notices are due. The esatblish date is permanent.

formula to update dates periodically
 

Suggest you try posting in .programming, then
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham" wrote in message
...
This would work except that the item maybe opened for an indefinite period
which would result in indefinite columns. The spreadsheet is very large
and
it is tediuos searching for overdue items every day. Is there a macros for
this purpose or a different approach to the problem.