I have a report in Crystal report that has a promise date and a shipped day, both data types are number. The data is stored as yyyymmdd in the database (DB2) so I created two formula fields to change the date format to display mm/dd/yyyy. I have two parameter "StartDate and EndDate" and the type is string. I need to find the difference between these two fields and display the date difference on the report.
Here is the formula fields I have created:
Formula field: Promise_date
If {Command.PROMISE_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.PROMISE_DT}/10000),
Truncate(Remainder({Command.PROMISE_DT},10000)/100),
Remainder({Command.PROMISE_DT},100))
Formula field: Shipped_day
If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))
Formula field: datediff
DateDiff("d",{@Shipped_day},{@Promise_dt})
When I enter the date in the parameter fields I have to enter it in as "yyyymmdd" for both fields then run the report. It pulls back data for the first page.
When I try to go to page two of the report I get an error "A month number must be between 1 and 12." Then the formula Editor opens for Shipped_day and highlights
If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))
Here is the formula fields I have created:
Formula field: Promise_date
If {Command.PROMISE_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.PROMISE_DT}/10000),
Truncate(Remainder({Command.PROMISE_DT},10000)/100),
Remainder({Command.PROMISE_DT},100))
Formula field: Shipped_day
If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))
Formula field: datediff
DateDiff("d",{@Shipped_day},{@Promise_dt})
When I enter the date in the parameter fields I have to enter it in as "yyyymmdd" for both fields then run the report. It pulls back data for the first page.
When I try to go to page two of the report I get an error "A month number must be between 1 and 12." Then the formula Editor opens for Shipped_day and highlights
If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))