Length function for Numbers

[RachelK]

Hi, Does anyone know how I could use the last two digits of a number.

I have a number called a period no which is 200210 the first 4 digits are the year and the last two are the month, the user enter's this number in as a parameter which automiaticall formats the number to 200210.00 I would like to write a formula that just uses the month part of the number.

So I would like something like (Right{?Period},2}, I can only do this if it's text I do not want to change the number to text then back to a number.

The result of the formula would be 10.

Any ideas I would be gratefull.

 

[HowardHammerman]

no problem.

You need the ToText function. For example:

right(totext({?period}),2)

Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[RachelK]

I would like to Avoid the totext function as I am sending my parameters through the web browser, so this can not be done for length or char in numbers with out changing a number to text.

Thanks, Rachel.

 

[dgillz]

Round(Value,-2) will round to the nearest 100. Since the last 2 digits represent months, and 12 is the highest value you can have, this will always round down.

So the formula you need is:

Value-Round(Value,-2)

Which will return 1 thru 12

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[HowardHammerman]

I disagree. I don't think that will work. Try this:

remainder({table.numberfield},2002)

that will divide each value by 2002 and only show what is left over. The left over part will be the month. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Ngolem]

ok my two cents on this....though I don't fully understand why the ToText option is not ok.

dgilz - you have the right idea but the wrong function as I will show...use Truncate not Round

HH - your solution has no flexability in that the year is hard coded


Here is my shot:

to get the month

{Table.PeriodNumber) - truncate({Table.PeriodNumber),-2)

to get the year

truncate({Table.PeriodNumber),-2)/100

make sure the fields containing these formulas are formatted with no decimal points and the thousands separator removed. Jim Broadbent

 

[Naith]

What's wrong with using the round function?

In my experience, both round and truncate will do pretty much exactly what Rachel's asking.

Naith

 

[Ngolem]

you want the truncate function because that is what you are doing...you aren't rounding. In this case it is moot perhaps. Jim Broadbent

 

[dgillz]

Ngolem-
As I stated in my post, round will work as the maximum number for the month will be 12. So round works fine in this context

Howards solution will work only if the year is 2002.

Try my solution, it works and I have tested it. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[HowardHammerman]

OK Guys, here is another version of my solution that will work for all years:

remainder({table.field},truncate({table.field}/100))

BUT

You guys are all brilliant. I never thought to use a negative number with the Round function. Good work.
Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269