0

I'm trying to write a function in Google Apps Script to manipulate durations calculated in Google Sheets. The cells holding the durations look like:

18:40:00
26:42:01

When I pass a duration into my code it appears to come in as a date object. So I've been using Date.getHours() et al to pull out the components of the time. But when the hours exceeds 23, it comes out mod 24.

Where did the "days" part of the duration go? Is there a method I can call on the Date object to get it?

  • By one hand, please avoid including more than one question in the body of a question, by the other hand asking for "best something" without describing what means best in the question context makes the question primarily opinion-based. – Rubén Dec 12 '19 at 15:35
3

Working with date, time and specifically duration is a pain in JavaScript, and Google Sheets does not make it much easier.

A JavaScript Date object (which is really a datetime object) is not suited for working with durations. A datetime is a lot more complex than it seems at first.

So when you ask

Where did the "days" part of the duration go?

... there is really no secure way of converting a duration to a number of days, since you don't know which day, in which timezone you're talking about. In my region, daylight savings time ended October 27, 03:00:00 (this year), meaning that a period spanning from October 27 00:00:00 through October 28 00:00:00 is actually 25 hours. Different countries have different rules for daylight savings time, adding to the confusion.

See this article for more examples on how dates are difficult to calculate with.

My advise would be to ditch the entire Duration feature, and decide which unit you want to work with: Days, hours or minutes. If you choose to work with hours, and want to specify a duration of 1 hour and 30 minutes, enter 1.5 in that cell.

If you need to display 1.5 hours as 1 hours 30 minutes somewhere, write a simple formula for that - but use it only for display purposes.


If you really want to work with Date, some experimentation on a simple spreadsheet might clarify a few things. I set up this spreadsheet for experimentation - feel free to copy it to your own drive.

In cell A2 I enter a number of days, e.g. 1.5. Cell B2 is formatted as Duration, with the formula =A2, so it simply displays 1.5 as a Duration: 24:00:00.

For cell C2 I have written a small script that dumps some debug information about the B2 value:

function outputValueInfo(value) {
  return [value, typeof value, JSON.stringify(value), value.getTime()];
}

This causes the C2-C5 to display

12/31/1899
object
"1899-12-31T11:00:00.000Z"
-2209035600000

So a duration of 1.5 days is represented by a Date object with the value 1899-12-31T11:00:00.000Z. Not the easiest thing to work with.

Enter a value of 0 in A2, and watch C4 change to "1899-12-31T11:00:00.000Z". We could use this as a base date, and compare it to the 1.5 days duration date.

"Subtracting" two dates is easiest done by looking at the date's getTime() function, which gives the number of milliseconds since the Unix Epoch, which is 00:00:00 UTC on 1 January 1970.

By comparing each duration with the base date, we can write a function:

function durationInDays(date) {
  var baseDate = new Date("1899-12-29T23:00:00.000Z");
  return (date.getTime() - baseDate.getTime()) / 1000 / 60 / 60 / 24;
}

Hopefully this can be a starting point for your calculation needs.

| improve this answer | |
1

Google Apps Script / JavaScript doesn't include a "native" way to handle durations but you could use a library like moment.js to add several methods to handle them but this could imply a steeped learning curve. A simpler solution is to read the duration from the spreadsheet as "displayed value" by using getDisplayValue instead of the "inherent value" got when using getValue, then use text handling methods to parse hours, minutes and seconds from the spreadsheet duration value.

Example

/**
 * Returns the hours part from a string (HH:mm:ss)
 *
 * @param {string} duration A string representing duration HH:mm:ss
 */
function getHours(duration){
  return parseInt(duration.split(':')[0]);
}

Related

Reference

| improve this answer | |
1

I think we can't manipulate duration calculated. We need to convert them to numbers first, and then we can manipulate number values calculated. After that, we convert them back to duration.

For example, we want to add 2 duration at 2 ranges durationRg1 and durationRg2 into the range totalDuragionRg.

totalDurationRg.setValue(Add2Duration(durationRg1, durationRg2));
//set the totalDurationRg to Duration format if you have not done it.
totalDurationRg.setNumberFormat('[h]:mm:ss');

function Add2Duration(durationRg1, durationRg2)
{
     durationRg1.setNumberFormat('#,##0.00');
     var dur1numVal = durationRg1.getValue();
     //  set back to duration format
     durationRg1.setNumberFormat('[h]:mm:ss');

     durationRg2.setNumberFormat('#,##0.00');
     var dur2numVal = durationRg2.getValue();
     //  set back to duration format
     durationRg2.setNumberFormat('[h]:mm:ss');

     return dur1numVal + dur2numVal;
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.