Problem with returned value 1

[dnfrantum]

I am using CR XI and I have created the following formula to evaluate and return the value of the previous period plus one. While it works to return the correct values, it returns them as numbers with the format of $2007.00-2. I am hoping someone out there can help me convert this to a format more like 2007-02.

Here is the formula:

ToText(ToNumber(Mid({@FirstPeriod},1,4)) & '-' & (ToNumber(Mid({@FirstPeriod},6,2))+1))




Thanks in advance,
Donald

 

[lbass]

I think you should convert your string to a date, because you will run into trouble when {@FirstPeriod} = 2007-12, since there is no month 13, obviously. Try the following:

//{@date}:
stringvar x := {@FirstPeriod};
date(val(left(x,4)),val(mid(x,6,2)),1)

Then you can use this in another formula to add months:

{@month3}:
dateadd("m",2,{@todate})

You can then use this in formulas in your mock crosstab (from your other post) by also converting your stringperiod field to a date as above and then comparing it to each month formula.

-LB