phone: (540)338-0194


 Crystal Reports formula examples

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.

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