crystal-reportsdatediff

Datediff formula in Crystal Report result wrong calculation

Here I'm using the following formula in my crystal report to find the aging of a product. I have taken my manufacturing Date as Default_Date and Date to compare as current date.

datediff("d",CDate({Yarn_Packing_Weighment_Entry.Default_Date}),CurrentDate)

The formula have no errors but when date is from 1st to 12th - The difference results are wrong

Date compared - Current Date ie 30-07-2018

and after 13th to 31st -

Which shows correct calculation after 12th

DOP Column date is which I'm comparing with Currentdate. A screenshot is attached

D1 Column is the date which results DOP converted to Cdate and D2 is CurrentDate

Please advice me where I'm wrong. Thanks in advance

Solution

  • I assume your database field {Yarn_Packing_Weighment_Entry.Default_Date} is in DD/MM/YYYY format. CDATE does not work well with these dates. It tries to but it doesn't work very well. I am sure there are many different creative ways to handle this but here is my offer

    datediff("d",
cdate(
split({Yarn_Packing_Weighment_Entry.Default_Date},"/")[2]
+"/"+
split({Yarn_Packing_Weighment_Entry.Default_Date},"/")[1]
+"/"+
split({Yarn_Packing_Weighment_Entry.Default_Date},"/")[3]
),
currentdate
)