CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dominik Lemberger
Dominik Lemberger

Reputation: 2426

Google Spreadsheet Makro - Add one time value to another

var valueDate = Utilities.formatDate(sheet.getRange("D2").getValue(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd/MM/YYYY hh:mm:ss");

var valueResetCD = Utilities.formatDate(sheet.getRange("C2").getValue(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "hh:mm:ss");

This are my 2 lines I currently have - As the Spreadsheet timezone may be different from the makro timezone I got told to always get the timezone from the Sheet.

In those 2 values I have the right values safed (tried to print them into a field)

first is 19/09/2020 22:34:52 (a specific date) and the second is 03:00:00.000 (3 Hours)

Now what I want to do is just add those 2 times together so I get as output: 20/09/2020 01:34:52

I tried it with: sheet.getRange("Data!$A$21").setValue(valueDate + valueResetCD) but I guess it is not that easy - it just copies the 2 string values of the times into one line.

How can I add those 2 Time / Date values to eachother so I get the output above

Upvotes: 1

Views: 51

Answers (1)

Tanaike
Tanaike

Reputation: 201428

I believe your situation and goal as follows.

  • In your situation, as a sample value,
    • valueDate is 19/09/2020 22:34:52 retrieved from sheet.getRange("D2").getValue().
    • valueResetCD is 03:00:00.000 retrieved from sheet.getRange("C2").getValue().
    • Both values are the date object.
  • You want to add valueResetCD to valueDate.
    • When the sample values are used, you want to get 20/09/2020 01:34:52 as the result value.

Modification points:

  • In this case, I think that valueDate is the correct date time like 19/09/2020 22:34:52 as the date object.
  • But, I think that valueResetCD is 1899/12/30 03:00:00. Because the Spreadsheet uses the serial number.
    • From this situation, it is required to convert the serial number to unix time.
    • And also, in this case, the time difference is required to be considered.
  • Both values are added by converting from the date object to the unix time. And, the result value is converted to the date object.

When above points are reflected to the Google Apps Script, it becomes as follows.

Modified script:

function myFunction() {
  var sheetName = "Sheet1";  // Please set the sheet name.
  
  // 1. Retrieve values from the cells as the date object.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var valueDate = sheet.getRange("D2").getValue();  // As a sample value, it's `19/09/2020 22:34:52`.
  var valueResetCD = sheet.getRange("C2").getValue();  // As a sample value, it's `03:00:00.000`.

  // 2. Convert the date object to the unix time.
  var valueDateUnix = valueDate.getTime();
  
  var differenceBetweenSerialAndUnix = new Date("1970/01/01 00:00:00").getTime() - new Date("1899/12/30 00:00:00").getTime();
  var timeDifference = (new Date().getTimezoneOffset()) * 60 * 1000;
  var valueResetCDUnix = valueResetCD.getTime() + differenceBetweenSerialAndUnix - timeDifference;
  
  // 3. Add both time and convert it to the date object.
  var resultDateObject = new Date(valueDateUnix + valueResetCDUnix);
  var resultString = Utilities.formatDate(resultDateObject, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd/MM/YYYY hh:mm:ss");
  console.log(resultDateObject)
  console.log(resultString)  // You can see "20/09/2020 01:34:52" at the log.
  
  // 4. Put the value to the cell.
  sheet.getRange("A1").setValue(resultDateObject);
}

Note:

  • In this answer, it supposes that the values from the cells "C2" and "D2" are the date object. If those values are the string which is not the date object, above script cannot be used. So please be careful this.

References:

Upvotes: 2

Related Questions