I had to write an interesting formula today which I thought I’d share with the world.

The challenge was to report on the duration field in conthist, and to round it up to the nearest five minutes. So, for example, 1 minute would become 5 minutes, 43 would become 45, etc. This was so that the report could be used to charge for support calls, where the client was charging in 5 minute increments.

The duration field is stored as a text field in the format hh:mm:ss and the formula converts this to seconds. Other formulae are then able to perform additions and totals on this.

The formula that does the conversion and the rounding up is this:

numbervar seconds:=((val(mid({CONTHIST.DURATION},1,2))*3600)+(val(mid({CONTHIST.DURATION},4,2))*60)+val(mid({CONTHIST.DURATION},7,2)));

numbervar rounding:=(300-(seconds mod 300)) mod 300;

seconds+rounding