formula for calculating dates 1

[Shusha]

Hi there,

Would appreciate any help given for this. I have a few dates but what i want is a field called TERMINATION Date that is calculated by the system with the dates i provide it with from a query that i run against my db. I am using cr 8.5.

THis is my case scenario. I need the report to create a field called termination date ( calculated thus ). IT is a wee bit confusing.. but let me give it my best shot in describing.. i need the system to calculate the termination date based on a childs age..I have shown in my example below what i do manually but wld like th system to calc it for me as the volume of cases keep going up.

The logic is that child's AGE is over 5 then add 365 to the date of commencement . If the child is under 5 then use the child's 6th birthdate as date of termination. Sounds simple when i explain but finding it difficult to put it into a formula.

the last 3 cases show how the date of termination of service is the 6th birth date.

client! DOB ! AGE ! Commencement !Termination
# ! ! ! date !date
-------------------------------------------------------
A001! 5/11/96! (6.9) !29-aug-02 ! 29-aug-03
B002! 6/20/96! (6.8) ! 8-jul-02 ! 18-jun-03
C003! 12-dec-98 (4.2) ! 7-aug-02 ! 12-dec-04
E005! 7-jun-99 (3.8) ! 7-aug-02 ! 7-jun-05
F006! 1-jul-99! (3.7) ! 1-aug-02 ! 1-jul-05

thnks for all ur help and input..

usha

 

[synapsevampire]

Try:

If datediff("yyyy", currentdate,{table.DOB}) >= 5 then
{table.Commencement}+365
else
dateadd("yyyy",6,{table.dob})

Keep in mind that adding 365 days to a date is NOT the same as adding a year because of leap years. If you meant that you wanted to add a year to the {table.Commencement} for >= 5 years old, use:

dateadd("yyyy",1,{table.Commencement})

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Shusha]

I think i have start looking in the dictionary for another word other than thanks. U have saved so much headaches for me K.. u are truly awesome.. thnaks a million., it worked liked a breeze..
usha