Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide On Location TrainingPublic ClassesIndividual TrainingIntroductory Course OutlineAdvanced Course OutlineFormula ExamplesNewsletter Back IssuesMy BlogResource LibraryConsulting ServicesSupport ServicesContact InformationLinks to third party productsBack to main pageMy Credentials
Free Crystal Reports formula examples from KenHamady.com


To learn the techniques used in these formulas get:


The Expert's Guide to Crystal Reports Formulas
and
 Expert Techniques for Crystal Reports I, II & III

PDF exPLODE

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]) )