Post Author: gigimonu
CA Forum: Formula
I wanted to write a formula (if there is a function I can use) that can convert a date difference to total number of years, months and days example
adate = 10/22/2006
?xdate = current date - adate (answer should be 1 years, 0 months and 0 days)
Please help
Thanks
Post Author: SKodidine
CA Forum: Formula
Check out this thread.
http://technicalsupport.businessobjects.com/cs/forums/thread/6084.aspx
Post Author: V361
CA Forum: Formula
[Years, Months, Days]
DATEVAR FROMDATE := DATE(2000,01,01); // FROM DATE
DATEVAR TODATE := CURRENTDATE; // TO DATE
//
NUMBERVAR YEARS;
NUMBERVAR MONTHS;
NUMBERVAR DAYS;
STRINGVAR DIFF;
DATEVAR TEMP;
//
IF TODATE < FROMDATE THEN
(TEMP := TODATE;
TODATE := FROMDATE;
FROMDATE := TEMP);
//
YEARS := DATEDIFF('YYYY',FROMDATE,TODATE);
//
IF YEARS > 2 THEN
(YEARS := YEARS - 2;
TEMP := DATE(DATEADD("M",YEARS * 12,FROMDATE));)
ELSE
(YEARS := 0;
TEMP := FROMDATE);
//
WHILE TRUE DO
(TEMP := DATE(DATEADD('M',1,TEMP));
IF TEMP > TODATE THEN
EXIT WHILE;
MONTHS := MONTHS + 1);
//
DAYS := DATEDIFF('D',DATE(DATEADD('M',-1,TEMP)),TODATE);
//
IF MONTHS > 12 THEN
(YEARS := YEARS + INT(MONTHS/12);
MONTHS := MONTHS MOD 12);
//
DIFF := IIF(YEARS>0 ,TRIM(TOTEXT(YEARS,0)) & " YEARS " ,"0 YEARS ") &
IIF(MONTHS>0,TRIM(TOTEXT(MONTHS,0))& " MONTHS ","0 MONTHS ")&
IIF(DAYS>0 ,TRIM(TOTEXT(DAYS,0)) & " DAYS" ,"0 DAYS");
//
Post Author: gigimonu
CA Forum: Formula
Thanks for the code. It works well if I hardcode the FROM DATE value to FROMDATE
DATEVAR FROMDATE := DATE(2000,01,01); // FROM DATE
But if I assign a field to FROMDATE it fails (it shows wrong years, months and days). see below
DATEVAR FROMDATE := date({PAEMPLOYEE.BEN_DATE_1}); (this fails)//DATEVAR FROMDATE := DATE(2006,05,30); // FROM DATE (this works)
Can you please help me on this. I need to assign a fields name to FROMDATE variable
Thanks for all your help
Post Author: gigimonu
CA Forum: Formula
before I try this, I just want to let you know that If I place this formula in Report Hearder section it works but if I try to put any where like page header, footer it fails. I have more than 4 subreports in my main report. Do you have any idea?
Thanks
Post Author: yangster
CA Forum: Formula
what do the subreports have to do with your formula?are you getting shared variables from them?and formulas should never be evaluated at the page header or footer level as they are reset at every change of page
Add a comment