formula to update dates periodically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Graham - 14 Apr 2008 21:29 GMT

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.

Reply to this Message

Max - 15 Apr 2008 00:46 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.

Reply to this Message

Graham - 15 Apr 2008 02:48 GMT

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.

> Some thoughts on a possible approach ..
>
[quoted text clipped - 26 lines]
> > 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.

Reply to this Message

Max - 15 Apr 2008 03:17 GMT

Suggest you try posting in .programming, then

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.

Reply to this Message