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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive