CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
TheTravloper
TheTravloper

Reputation: 246

Calculate the duration difference between 2 formatted time fields in Google App Scripts

I have been trying to get duration between 2 formatted time columns (Start Time & End Time). Newbie with the Google App Scripts. Please guide me how to achieve that.

var a = e.source.getActiveSheet().getRange(row,7).getValue()
var b = e.source.getActiveSheet().getRange(row,8).getValue()
var c = (b-a).getTime();
e.source.getActiveSheet().getRange(row,9).setValue(c);

Here is an image attached for the google sheet

Upvotes: 0

Views: 450

Answers (2)

Bill Pratt
Bill Pratt

Reputation: 15

I know this is a four year old thread but I wanted to add to @Jescanellas solution.

Pick up at var hours

var hours = (b1 - a1) / 3.6e+6 /24; e.source.getActiveSheet().getRange(dataRow,14).setNumberFormat('h:mm:ss') e.source.getActiveSheet().getRange(dataRow,14).setValue(hours);

By adding the bold, it will display as regular time in hours minutes seconds. (in my .getRange, dataRow is the variable I am using). Thought this might help someone that sees this thread. It took me a while to figure it out.

Upvotes: 0

Jescanellas
Jescanellas

Reputation: 2608

The first thing you need to know is how Sheets and Apps Script interpret date formats. Google uses Unix time on it's products, so it starts at 00:00:00 UTC on 1 January 1970. Not specifying the date means Sheets interprets 09:00:00 as Sat Dec 30 17:09:21 GMT+00:09 1899.

In your case it wouldn't make a major difference since you are deducing dates, but I recommend you write them like 30/05/2020 09:00:00, and then go to Format > Number > Time.

Regarding Apps Script, getValues will interpret it as something similar to Mon Dec 30 18:00:00 GMT+02:00 2020, which is not useful. Use the Date Javascript method to format it to a Date object. Then you can use getTime to get it in milliseconds (everything here works in milliseconds):

Once you have both time values in milliseconds, you can operate with them. You have to convert them to hours (1 milliseconds = 3.6e+6 hours):

var a = e.source.getActiveSheet().getRange(row,7).getValue()
var b = e.source.getActiveSheet().getRange(row,8).getValue()

var a1 = new Date(a).getTime(); //Time in milliseconds
var b1 = new Date(b).getTime(); //Time in milliseconds

var hours = (b1 - a1) / 3.6e+6;

e.source.getActiveSheet().getRange(row,9).setValue(hours);

This won't work well for minutes. 1h and 30 minutes will be 1,5 hours.

Function's solution:

Instead of Apps Script, use Sheets formulas to calculate it. It's as simple as setting, for example C1, to =B1-A1;

enter image description here

Upvotes: 1

Related Questions