Thursday, July 25, 2013

Google Spreadsheet Sum values in hh:mm:ss.SSS format

Question:

( by 
Rick Robinson-Donnellan )

Hi guys

I've done a bit of searching but not been able to find the answer I was looking for.

Basically, I have two lap times (think racing) in the format of:

MM:SS:000 (tenths, hundreds, thousands)

so for example 1:37.456 

I then have another lap time, 1:37.978

I want to be able to add the two times together and come up with the total race time. Every time I do it, it comes up with a bad value and I can't see any option for it in the formatting. I know it must be easy to do but I don't know how.

1 rule however: The two lap times must be simple i.e. I need to be able to just type/copy and paste the numbers in without any formatting and then the Total field work out the rest for itself. I don't want to have to enter formulae into both each lap time fields if that makes sense. I just want the total to work it all out itself.

Thanks in advance
All the best
Rick

Solution:

Have a look at the following screenshot:





I have the following formula in Cell B4:

=KSUM(B1;B2)


the above formula is a custom function KSUM() that I have written and inserted in Script.

Have a look at the following code:

///////////////////////////////////////

function KSUM() {
  var value1 = arguments[0].split(".");
  var value2 = arguments[1].split(".");
  var time1 = value1[0].split(":");
  var time2 = value2[0].split(":");
  var ms       = parseInt(value1[1],10) + parseInt(value2[1],10);
  var seconds1 = parseInt(time1[time1.length-1],10);
  var seconds2 = parseInt(time2[time2.length-1],10);
  var minutes1 = parseInt(time1[time1.length-2],10);
  var minutes2 = parseInt(time2[time2.length-2],10);
  var hours1   = parseInt(time1[time1.length-3],10);
  var hours2   = parseInt(time2[time2.length-3],10);
  var seconds=0,minutes=0,hours=0;
  
  if( seconds1 > 0 && seconds2 > 0 ) seconds = seconds1+seconds2;
  else if( seconds1 > 0 ) seconds = seconds1;
  else if( seconds2 > 0 ) seconds = seconds2;
  
  if( minutes1 > 0 && minutes2 > 0 ) minutes=minutes1+minutes2;
  else if( minutes1 > 0 ) minutes = minutes1;
  else if( minutes2 > 0 ) minutes = minutes2;
  
  if( hours1 > 0 && hours2 > 0 ) hours=hours1+hours2;
  else if( hours1 > 0 ) hours = hours1;
  else if( hours2 > 0 ) hours = hours2;
  
  var total_ms = parseInt(hours*60*60*1000,10)+parseInt(minutes*60*1000,10)+parseInt(seconds*1000,10)+parseInt(ms,10);
  var d = new Date(total_ms);
  
  if(d.getUTCHours()) return d.getUTCHours() + ":" + f00(d.getUTCMinutes()) + ":" + f00(d.getUTCSeconds()) + "." + f000(d.getUTCMilliseconds());
  else return f00(d.getUTCMinutes()) + ":" + f00(d.getUTCSeconds()) + "." + f000(d.getUTCMilliseconds());
};
function f00(n) { return n < 10 ? '0' + n : n; };

function f000(n) { return n < 10 ? '00' + n : n < 100 ? '0' + n : n; };


///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.



And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

8 comments:

  1. Hi, I tried using your script but I got this error:
    TypeError: Cannot call method "split" of undefined. (line 2, file "Code")

    Also, can your script handle more than two numbers? Thanks!

    ReplyDelete
    Replies
    1. Hi Xelotath,

      I can't say why you are getting that error without looking at your spreadsheet, so if you wish then you can share your spreadsheet, I'll try to help you out!

      And this script will take only two numbers... if you want more then you'll need to modify the code..

      Delete
    2. I figured my error, I was putting a : instead of ; in the formula. And I got over the 2 numbers limit by doing calculations two by two. Thanks for making the script!

      Delete
  2. When I sum two cells and one of them is blank, it gives back 'NaN:NaN.NaN". Shouldn't instead just give back the result like any number summed to zero?

    ReplyDelete
  3. Would you undertake a private project to develop this script a bit more, so that it can do some lap time differences by subtracting etc?

    ReplyDelete
    Replies
    1. Yes sure, send me details at kishan.pionero@gmail.com

      Delete
    2. If you have completed the work on this would you be able to send me the script or any way of figuring it out to charlesdraycott@outlook.com

      Many thanks,
      Chaz

      Delete