Crystal Report with DateDiff 1

[ms901Boss]

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

 

[pmax9999]

Hi ms901Boss.

There is nothing wrong with your formulas, provided the data is consistently stored in the format you state. If the report is crashing, it is because of inconsistent data, rather than your formulas.

Given your report is using a Command, I'd suggest you amend the command to return the two date fields as 'proper' Dates (or Date/Times), and a date calculation to return the number of days. This way you don't need to use any formulas within crystal.

In any event, the starting point in troubleshooting this issue is reviewing all the PROMISE_DT and SHIP_DT data returned by the command to identify the data that isn't in the expected format.

Hope this helps.

Cheers, Pete

 

[ms901Boss]

Thanks pmax9999,

you helped me resolved the issue. I had a date of 99999999 in the SHIP_DT and needed to add it to the formula field


If {Command.SHIP_DT} = 0 then date(0000,00,00) else
[highlight #FCE94F]If {Command.SHIP_DT} = 99999999 then date(0000,00,00) else[/highlight]
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))

 

[ms901Boss]

Now I am trying to create two more formula fields: OnTime and Late using the data from the DateDiff formula.

OnTime formula:

if {@dateDiff} > 0 then Count ({@dateDiff})

I get 0.00 for this formula but should get 25 ( I am trying to get all numbers that are greater than 0)

Late formula:

if {@dateDiff} < 0 then Count ({@dateDiff})
I get 42.00 for this formula but should get 8 ( I am trying to get all number lower than zero)

 

[pmax9999]

Glad it helped.

You have a couple of choices to achieve what you want. Yo can use either a Running Total to count the results (Count: suitable field, Evaluate: formula > or < than 0, Reset: Never), or use a formula like:

[Code {@On_Time}]
if {@dateDiff} > 0 then 1
[/Code]

, then add a summary to Sum the formula field (place formula fields in details section, suppress if required, right click on formula and select Insert Summary ==> Sum).

Hope this helps.

Cheers
Pete

 

[ms901Boss]

Thanks again pmax9999 I got the sum for onTime and late. You are the BEST!!!!