Google Script; How to SUM two date and return the result in google spreadsheet

Question

Data:

E3 = 5/12/2013 18:26:00
C3 = 00:15:00

Result should be = 5/12/2013 18:41:00

Problem:

I want an script that sum E3 + C3 and return the value. I needs this to integrate the information with other script.

I try to SUM using script, but in my research is needs some kwnowloge regarding milliseconds the date to bit or something like this to sum date.

The resuld should be in that format dd/mm/yyyy HH:MM:SS (24hs) because is used to call other script.

Test Case:

Workaround: Here

I will appreciate if anyone can help. I think this script will helpfully some much because is a general function.

The key thing is to understand that the need is to add a period to a date, and not a date to a date (which does not make sense mathematically). Dates/times (and both the fields in question are treated as dates in the spreadsheet) will create javascript dateobjects if referenced as range values in a script and immediately bring problems of base date, time zone better to do the calc on the spreadsheet side (either creating a period in milliseconds to be used by the script or a the result as a formula) — DavidF
– DavidF, Commented Dec 6, 2013 at 23:57

wchiquito · Accepted Answer · 2013-12-06 09:54:57Z

1

One option is to establish a formula, in a new cell, e.g.:

enter image description here

The following code sets the formula:

/* CODE FOR DEMONSTRATION PURPOSES */
function dateAdd() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = ss.getRange('C1').setFormula('A1+B1');
  var formattedDate = Utilities.formatDate(range.getValue(), 'GMT', 'dd/MM/yyyy HH:mm:ss');
  //Logger.log(formattedDate); <-- 05/12/2013 18:41:00
}

It is important to correctly define the timezone of the spreadsheet and script.

answered Dec 6, 2013 at 9:54

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kumara Guru Over a year ago

Is it possible to apply the above app script to apply in a loop i.e I have a column to create by adding two columns

Collectives™ on Stack Overflow

Google Script; How to SUM two date and return the result in google spreadsheet

Problem:

Test Case:

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

Test Case:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related