Convert number in an array

[sugabutt]

I have a field that is numeric that holds our fiscal year and period, but I want to convert it a month in it's word format, and skip the date, and display the year.

Data example: 10901 is the period number for July 2008. We run our fiscal year from July to June. Today, for example, is August 2009, but our period number is 11002 (the first 1 is a century number, the '10' stands for fiscal year 2010, and the '02' is August, which is the 2nd month in this fiscal year).

So do I need to build an array or something to display this - I want it to show "August 2009".

 

[lbass]

As I said in your other post, all you need to do is right click on the conversion formula->format field->date->choose "March 1999".

Or you can write a formula:

totext({@date},"MMMM yyyy") //where {@date} is the conversion of number to date formula from your other post

-LB

 

[sugabutt]

This is a different thing entirely. This field is not a date. It holds our period numbers. The other field that I asked about in my other post was an actual date.

For example, the data for our period number is 11002. The first 1 is a century number which I don't need, the '10' stands for fiscal year (not regular year) 2010, and the '02' is August, which is the 2nd month in this fiscal year.

So I cannot just use the Date or a MonthName funtcion, because in this report, month 01 is really July, not January. And the year starts 6 months before the actual calendar year starts.

 

[lbass]

Sorry. I would still use the same approach though. Create a formula {@period} like this:

stringvar x := totext({table.period},0,"");
datevar y := dateserial(2000+val(mid,x,2,2))-1,val(right(x,2))+6,1);
totext(y,"MMMM yyyy")

Note the -1 for the year and the +6 for the month and the use of dateserial.

-LB

 

[sugabutt]

LB, I've tried the formula you gave, but it says 'the remaining text does not appear to be part of the formula' and it hightlights the part after the -1 in the 2nd line. I've played around with the parentheses but cannot get the formula to be error-free. Can you check it again please?

 

[lbass]

That should be:

stringvar x := totext({table.period},0,"");
datevar y := dateserial(2000+val(mid[red]([/red]x,2,2))-1,val(right(x,2))+6,1);
totext(y,"MMMM yyyy")

-LB

 

[sugabutt]

Thank you so much LB - that worked great! I can't believe how short that formula is, that does this complex formula.