need duration of 2 dates for CR 8.5

[dgoss]

I have a report that shows the start date and the end date for each record. These 2 fields come from seperate tables, the data type for each is string, 8, format, yyyymmdd (20020402). I need to get the duration between these 2 dates. How is this best accomplished in CR 8.5. Thanks in advance for your help.

 

[jodo]

Look in the formula editor. There is a
DateDiff(IntervalType, startDateTime, EndDateTime) function there.

 

[dgillz]

Are you sure the date is string and not integer? Several packages, most notably Macola and ACCPAC, store the dates as an 8 digit integer in the YYYYMMDD format.

Having said that, try the following formulas:

If the date is an integer:
Datediff(NumberToDate({FirstDate}),NumberToDate({SecondDate}))

If it is text:
Datediff(NumberToDate(ToNumber({FirstDate})),NumberToDate(ToNumber({SecondDate})))

NumberToDate is a UFL that can be found on the Crystal Decisions website, or I can email it to you if you would like.



Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[dgoss]

Yes, the field is string. I'll try getting the NumberToDate, and see if that works.

I have tried the DateDiff function too. It keeps telling me that it wants an actual date, I want to use the fields.

Thanks!

 

[dgillz]

Dgoss-

You are on the right track, you just have to convert that string to a date like my second formula does. Then the Datediff() function will not return an error. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[dgoss]

I think I'm almost there!! But I believe somewhere the syntax is wrong, Help!

Datediff(NumberToDate(ToNumber({DH_DOCUMENT.DH_ISSUED_DATE}),NumberToDate(ToNumber({DV_DOC_APPROVAL.DV_READY_DATE})))

 

[dgillz]

Datediff(NumberToDate(ToNumber({DH_DOCUMENT.DH_ISSUED_DATE})),NumberToDate(ToNumber({DV_DOC_APPROVAL.DV_READY_DATE})))

You were missing one &quot" to the immediate right of {DH_DOCUMENT.DH_ISSUED_DATE}
Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[dgoss]

You were right and I also needed the "interval type" at the beginning. It worked, the report is done!
Thanks so much for your help!!