Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Periodically Store/Save Excel DDE Values | Excel Discussion (Misc queries) | |||
Periodically listing files in a folder | Excel Discussion (Misc queries) | |||
Periodically exporting Excel sheet to HTML | New Users to Excel | |||
No update of dates | Excel Discussion (Misc queries) | |||
Dates that automatically update ... | Excel Discussion (Misc queries) |