Date Calculation - Days

[gennaroalpha7]

Hello -

I would like to calculate the days between two fields/dates - {CallLog.RecvdDate} and {CallLog.ClosedDate} both of these fields are strings.

I would also like to subtotal the total number of days for each group, and the total number of days for all the groups/departments


Thanks for your help.

G.

 

[pmax9999]

I recall from your previous threads that dates in your system are all stored as text. Therefore you need to convert them to dates and simply subtract the earlier date from the later date. You could use DateDiff but it is not necessary if the result you are looking for is the number of days. Something like this should work:

Date({CallLog.ClosedDate}) - Date({CallLog.RecvdDate})

This formula could be summed at the group level using a simple Summary.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

I tried using this formula...

DateDiff ("d", Cdate({CallLog.RecvdDate}), Cdate({CallLog.ClosedDate})) as a formula. Would this work?

But I can see that your solution is better.

Thank you and have a good.

Gennaro

 

[gennaroalpha7]

Pete -

Sorry to bug you again.

I added the ,@ResolutionTime, formula above that you suggested, and have added this...{CallLog.CallID} = Next( {CallLog.CallID}) in the details section to eliminate the duplictes.

But when I add a (subtotal) summary at the, Asgnmnt.GroupName, group level its not counting the days, it's counting all the tickets even the duplicates.

Am I missing something?

Thanks.

G.

 

[gennaroalpha7]

Hi -

Also getting an error from....

[highlight #FCE94F]Date({CallLog.ClosedDate})[/highlight] - Date({CallLog.RecvdDate}) with the first part highlighted in yellow. This seems to be working, but when I add subtotal summary and scroll through the pages Crystal takes me back to the formula and gives me the "Bad Date Format String"

Thx.

G

 

[kray4660]

The error is because one or more of the values for {CallLog.ClosedDate} is not a recognizable date (null?).

 

[gennaroalpha7]

Hi -

How can the formula be changed to avoid this error.

Thx.

G

 

[pmax9999]

I'm pretty sure we have covered this with you in previous threads Gennaro.

Take a look at the IsDate function in the Help files.


Pete

 

[gennaroalpha7]

I replaced 'Date' with 'ISDate' and still getting the same error....like so...

ISDate({CallLog.ClosedDate}) - ISDate({CallLog.RecvdDate})

I will look at the previous threads for a clue...

G.

 

[pmax9999]

Did you even bother to look at the help files? I wasn't suggesting that you replace Date with IsDate.

IsDate is a test to check whether the string can be converted to a "real" date.

 

[gennaroalpha7]

Yes, I checked it out, but it seems like there's an easier solution...than these two examples...

Local StringVar s := GroupName ({Asgnmnt.GroupName}, "monthly");

If IsDate(s) Then
CDate(s)
Else
CDate(0,0,0)

Or this one...


If IsDate({CallLog.ClosedDate}) and
IsDate({CallLog.RecvdDate}) and
IsTime({CallLog.ClosedTime}) and
IsTime({CallLog.RecvdTime}) and
(
OnLastRecord or
{CallLog.CallID} <> Next({CallLog.CallID})
)
Then seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvdDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else seconds := 0;

Tseconds := Tseconds + seconds;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'



It seems like I can try to modify the second example to what I am trying to do...or confuse myself...

Thx.

G.

 

[gennaroalpha7]

Hi -

I finaly got it working with this formula and using a running total.

If isdate({CallLog.ClosedDate}) AND isdate({CallLog.RecvdDate}) then

Date ({CallLog.ClosedDate}) - Date ({CallLog.RecvdDate})

else

0

Thanks for the guidance.

G.