Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
Free
Crystal Reports formula examples from
KenHamady.com
To
learn
the techniques used in these formulas get:
Numeric Date to True Date:
This formula is for
converting
Dates stored in the 8-digit numeric format YYYYMMDD into true
date
vales (i.e. Macola Accounting). One problem you many encounter is
that
the data may contain zero values, which will cause the formula to
choke.
I put a phrase in front of it that checks for a minimum value before
calculating
the date. To use this formula, simply copy it into your report and put
your
field name in on the second line in place of the field name I have used.
WhilePrintingRecords;
NumberVar input :=
{table.StartDate};
// or whatever your field name is
// This line checks for a
minimum
value, any value will work.
If input < 19590101
then
Date (1959, 01, 01) else
Date ( Val (ToText
(input,
0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )
The following formula can be used with a 6-digit numeric format that
stores
the values as MMDDYY.
WhileReadingRecords;
NumberVar input := {@test}; // or whatever your field name is
//this checks for numbers too low to convert and then zero pads the
input
to 6 characters
If input < 10101
then StringVar input2 := "010110"
else input2 := ToText (input, "000000") ;
//The first line assumes that any year below 50 is in 2000 while any
number
above 50 is in 1900
//You can replace the number 50 with any number you want.
Date ( Val (input2 [5 to 6]) + (if Val (input2 [5 to 6])
<
50 then 2000 else 1900) ,
Val (input2 [1
to
2]),
Val
(input2
[3 to 4]) )