CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Mateusz Pusz
Mateusz Pusz

Reputation: 1393

How to add hours from 2 cells?

I have some google spreadsheet logbook where I store duration of some activities in hours format [[HH]:MM:SS]. The spreadsheet adds such cells with no issues. However when I try to add them via Google Script I get some garbage. What I found is that Date() object is implicitly created for such cells, but I cannot find API of that value type.

I know I can convert the data to "hour integers" by multiplying them by 24 but that is a nasty workaround as it demands duplication of many cells. I would rather like a solution that will allow to do that in google script itself.

Upvotes: 0

Views: 174

Answers (1)

Serge insas
Serge insas

Reputation: 46792

here is a working function that does the trick.

I first tried to format it as a date but 36 hours is not really standard !! so I did a little bit of math :-) )

To get it working you should set a cell somewhere with value 00:00:00 that we will use as a reference date in spreadsheet standard. in my code it is cell D1(see comment in code, reference date in SS is in 1900 and in Javascript is in 1970 ... that's why it is a negative constant of 70 years in milliseconds...)

here is the code and below a screen capture of the test sheet + the logger It would be a good idea to modify this code to make it a function that takes cell value as parameter and returns the result as an array for example ([h,m,s] or something similar), this code is only to show how it works.

function addHoursValues() {
  var sh = SpreadsheetApp.getActive()
  var hours1 = sh.getRange('A1').getValue();
  var hours2 = sh.getRange('B1').getValue();
  var ref = sh.getRange('D1').getValue().getTime();
//var ref = -2209161600000 // you could also use this but it would be less obvious what it really does ;-)
Logger.log(ref+' = ref');
  var h1 = parseInt((hours1.getTime()/3600000)-ref/3600000);
  var h2 = parseInt((hours2.getTime()/3600000)-ref/3600000);
Logger.log(h1+' + '+h2+' = '+(h1+h2))
  var m1 = parseInt((hours1.getTime()-h1*3600000-ref)/60000);
  var m2 = parseInt((hours2.getTime()-h2*3600000-ref)/60000);
Logger.log(m1+' + '+m2+' = '+(m1+m2))
  var s1 = parseInt((hours1.getTime()-h1*3600000-m1*60000-ref)/1000);
  var s2 = parseInt((hours2.getTime()-h2*3600000-m2*60000-ref)/1000);
Logger.log(s1+' + '+s2+' = '+(s1+s2))
  var ts=s1+s2
  var tm=m1+m2
  var th=h1+h2
    if(ts>59){ts=ts-60;tm++};
    if(tm>59){tm=tm-60;th++}
Logger.log('sum = '+th+':'+tm+':'+ts)
}

enter image description here

enter image description here

EDIT : here are 2 "function" versions with corresponding test functions that show how to use it

function getHMS(hrs) {
  var t = hrs.getTime()/1000;
  var ref = -2209161600;
  var h = parseInt((t-ref)/3600);
  var m = parseInt((t-h*3600-ref)/60);
  var s = parseInt(t-h*3600-m*60-ref);
  return[h,m,s];// returns an array of 3 discrete values
}

function testHMS(){
  var sh = SpreadsheetApp.getActive();

  var hours1 = sh.getRange('A1').getValue();
  var hours2 = sh.getRange('B1').getValue();

  var sumS = getHMS(hours1)[2]+getHMS(hours2)[2];// add seconds
  var sumM = getHMS(hours1)[1]+getHMS(hours2)[1];// add minutes
  var sumH = getHMS(hours1)[0]+getHMS(hours2)[0];// add hours
    if(sumS>59){sumS=sumS-60 ; sumM++}; // handles values >59
    if(sumM>59){sumM=sumM-60 ; sumH++}; // handles values >59
  Logger.log(sumH+':'+sumM+':'+sumS);
}

OR

function addHMS(hrs1,hrs2) {
  var t1 = hrs1.getTime()/1000;
  var t2 = hrs2.getTime()/1000;
  var ref = -2209161600;
  var h = parseInt((t1-ref)/3600)+parseInt((t2-ref)/3600);
  var m = parseInt((t1-parseInt((t1-ref)/3600)*3600-ref)/60)+parseInt((t2-parseInt((t2-ref)/3600)*3600-ref)/60);
  var s = parseInt(t1-parseInt((t1-ref)/3600)*3600-parseInt((t1-parseInt((t1-ref)/3600)*3600-ref)/60)*60-ref)
         +parseInt(t2-parseInt((t2-ref)/3600)*3600-parseInt((t2-parseInt((t2-ref)/3600)*3600-ref)/60)*60-ref);
    if(s>59){s=s-60 ; m++}; // handles values >59
    if(m>59){m=m-60 ; h++}; // handles values >59
  return[h,m,s];// returns sum in an array of 3 discrete values
}

function othertestHMS(){
  var sh = SpreadsheetApp.getActive();

  var hours1 = sh.getRange('A1').getValue();
  var hours2 = sh.getRange('B1').getValue();
  Logger.log(addHMS(hours1,hours2));
}

Upvotes: 2

Related Questions