Skip to Content
Former Member
Jul 19, 2007 at 05:21 AM

calc datediff in seconds, then convert to mins then calc median in a group


Post Author: jfitz

CA Forum: Formula


I am trying to create a report which shows the MEDIAN of the time difference between two times. I am using the datediff function - doing the calc in seconds but wanting to display in mm:ss. I then want to apply a summary of median for this field in Group 1/Group 2 Header.

I have tried two ways and have the following problems (my knowledge of Crystal reports is limited so realise there may be other ways of doing this):

1. Doing the datediff in 'seconds', then converting to minutes using a formula:

Either: datetimevalue('Time Diff'}/86400), or the formula outlined in KB C2010260. But neither of these allows a summary of median.

2. Doing the datediff and then converting to minutes and doing the median calc in the same formula: datetimevalue(median('TimeDiff'},)/86400)

This works fine for Group 1 but is giving me an error for Group 2 - 'there must be a group that matches this field' (I have triple checked the spelling in my formula.

- any help is much appreciated - thanks