Skip to Content
0
Feb 20, 2009 at 01:55 PM

Bi-yearly date formula

41 Views

I have been instructed to create a report showing, in line chart form, the number of Corrective Action Requests over 5 years shown in 6 month increments. I should have 10 data points on the chart. I know very little about writing formulas least of all formula's using variables. I'm using Crystal XI and a SQL database. Can anyone provide assistance? I've added the formula I'm using below:

local datevar FieldDt;

if isnull({CORRECTIVE_MNT_V.SYMP_DATE}) then FieldDt:=date(1900,01,01) else

FieldDt:=date({CORRECTIVE_MNT_V.SYMP_DATE});

//Bi-Yearly

if datediff('D', {?Pm-@LowerDate}, {?Pm-@UpperDate}) <= 1825 then

totext(Year(FieldDt),0,"") + '-' +

(This is the point where I'm lost) right(('000' + totext

I would like the graph to display "1st Half 2XXX" or "2nd Half 2XXX" for each of the 5 years displayed. I simply don't know how to format the formula to return the text "1st Half 2XXX or 2nd Half 2XXX depending on which half the month falls. Currently my graph returns, for example 01/2004 7/2004 1/2005 7/2005 etc.