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.

Local NumberVar input := {table.StartDate}; // use your field name
// This line checks for a minimum value, any value will work.
Local StringVar DateString :=
If input < 19500101 then '00000000' else Totext (Input, 0, '');

Date (
Val (DateString [1 to 4]),
Val (DateString [5 to 6]),
Val (DateString [7 to 8]) )

The following formula can be used with a 6-digit numeric format that stores the values as MMDDYY.

Local NumberVar input := {table.StartDate}; // use your field name
//this checks for numbers too low to convert and then zero pads the input to 6 characters
Local StringVar DateString :=
If input < 10101
then "010101"
else ToText (input, "000000") ;

Local numberVar Yr := Val (DateString [5 to 6]);
//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 another number if needed
Date(Yr +(if Yr < 50 then 2000 else 1900),
Val (DateString [1 to 2]),
Val (DateString [3 to 4]))