I
I
Igor_092020-06-26 11:15:22
Google Apps Script
Igor_09, 2020-06-26 11:15:22

How to calculate time difference between two cells?

I am very superficially familiar with programming on Google apps script, based on my knowledge I wrote the following code (I’ll say right away that it doesn’t work correctly):

function ResponseTime(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
  var range = sheet.getActiveRange();
  var data = range.getValues(); 
  if(sheetName == "Вопросник" && range.getColumn() == 17){
    for (var i=0;i < data.length;i++){
      if (data[i][0]>0) {
        (data[i][0])=Date.parse(data[i][0]); 
        var time1 = (data[i][0])}
      else {
      }
    }
  }
  if(sheetName == "Вопросник" && range.getColumn() == 18){
    for (var i=0;i < data.length;i++){
      if (data[i][0]>0) {
        (data[i][0])=Date.parse(data[i][0]); 
        var time2 = (data[i][0])}
      else {
      }
    } 
   for (var i=0;i < data.length;i++){
      if (data[i][0]>0 && time2>0) {
        let date = new Date(time2);
        range.offset(i,+1,1,1).setValue(date.getHours()+':'+date.getMinutes());}
      else {
      }
   }
  }
}


I have two dates in the table in columns 17 and 18, I didn’t really find how to calculate the difference between them, so I decided to first get a timestamp from both cells using Date.parse (str), then find the difference in milliseconds and convert it to " hours:minutes". But in the end, what he planned, it was not possible to get. As I understand it, the main problem with the .getColumn() method is that it allows you to work with a certain range, that is, with only one column, and it does not suit me. What is the method to replace it? and I also assume that I wrote a lot of extra in the code, can it somehow be simplified, something thrown away? Perhaps I initially went the wrong way and everything can be implemented much easier? I will be glad to any suggestions.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
R
robprane, 2020-06-26
@robprane

Everything has already been thought of. https://support.google.com/docs/answer/6055612

G
Grigory Boev, 2020-06-26
@ProgrammerForever

Can you make it easier with formulas? The difference between the dates is a direct specific difference, because dates is the number of days since a particular date.
Describe in words - what needs to be done? And attach a sign - it will speed up the decision.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question