help creating projected dates

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i have a dba that keeps track of the clients that come and
go from a specific program (1 client could have more than
1 termination date in a given year, because the client may
leave and come back in the same year). tblclientinfo has
client name, address, phone. tblclientdates has startdate,
termination date. when a client leaves the program the
manager has to follow-up with this client 4 times (1
month, 3 months, 6 months and 1 year after the termination
date). i have tblfollowup witch is follow-up method
(could be phone, letter or face-to-face), date of follow-
up, yes/no checkbox(follow-up completed) then a comment
section.

i need to run a report that will show the manager who
needs a follow-up each month for the most recent
termination date. (ex. in Feb. who needs a 1 month follow-
up, who needs a 3 month follow-up etc). I'm not sure how
to do it because there are different follow-up times.

any suggestions on how to even start this would be greatly
appreciated:)
 
Hi nydia!
From what it sounds like you would need to create
another column which will first test to see if the
current monthh is 1,3,6 or 12 months after the
termination month. You can do this all in one, or make
one column for each test. I'm not an expert on Excel
dates, but I'm sure you can round to the termination date
to the begining of the month, and for each test add to it
1,3,6, or 12. For example if your termination date is in
column A, and your test for 1 month followup is in column
B, try in B1 =IF(DATE(YEAR(A1),MONTH(A1)+1,1)=DATE(YEAR
(TODAY()),MONTH(TODAY()),DAY(TODAY())-DAY(TODAY())
+1),"Client needs 1 month followup","") and copy down.
It's not pretty, but I think it works. Where is has +1
after the month, just change it to +3, +6, +12 etc for
your other tests and change the text to whatever you like.

Scott
 
Actually, now that I think about it =IF(DATE(YEAR
(A1),MONTH(A1)+1,1)=DATE(YEAR(TODAY()),MONTH(TODAY
()),1),"Client needs 1 month followup","")is alittle
shorter and does the same thing. I guess I know alittle
more about dates than I did 15 minutes ago.

Good luck!
Scott
 
Back
Top