Tinkering with SUM(), Durations & Grand Totals

In my last post I mentioned Jonathan Drummey’s math formula for displaying duration in hh:mm:ss format. Here it is again:

//replace [Seconds] with whatever field has the number of seconds in it
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes
+ INT([Seconds]/3600) * 10000 //hours

This works well until you turn on the Grand Totals. Then everything falls apart, or at least GT sum to incorrect values. I tinkered with it a bit, but eventually I reached out to Jonathan, and as always he sent me back the solution. Instead of summing the entire calculation, we need to sum each individual part. Here is the new formula Jonathan sent me:

//replace [Seconds] with whatever field has the number of seconds in it
IIF(SUM([Seconds]) % 60 = 60,0,SUM([Seconds]) % 60)// seconds
+ IIF(INT(SUM([Seconds])/60) %60 = 60, 0, (INT(SUM([Seconds])/60)) %60) * 100 //minutes
+ INT(SUM([Seconds])/3600) * 10000 //hours

Jonathan thanks again for the help.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s