Google-sheets – Working With Durations In Google Apps Script

google sheetsgoogle-apps-scriptjavascript

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?

Best Answer

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.

Related Topic