Calculate time range and parse date

Question

I have simple spreadsheet and need to calculate time ranges, according to the image below:

enter image description here

My rule is basically a simple math operation: (end date - initial date) - "09:00:00". My result column gives me something like: -0:21:00 and it's correct.

I have my columns formatted as "hour" (Format > Number > Hours). I need to get the sum up of third column and send the result through e-mail. Part of the code was intentionally omitted. Below my script snippet:

var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var to = row[0]; // e-mail
    var total = row[1]; // total column
    msg = "Total hours: " + Utilities.formatDate(total, "GMT-0300", "HH:mm:ss");
    subject = "Test";   
    MailApp.sendEmail(to, subject, msg);
  }

When I try to format Utilities.formatDate(...) before sending e-mail, the hour goes wrong. I've tried to remove Utilities call, passing the hour directly, but it gives me the following result:

Fri Dec 29 1899 23:45:28 GMT-0300 (BRT)

I realized where is my total var must be a JavaScript Date object, but it didn't work also. I need that my result column were send by e-mail.

have also a look at this post that might interest you :http://stackoverflow.com/questions/17715841/gas-how-to-read-the-correct-time-values-form-google-spreadsheet/17727300#17727300 — Serge insas, Sep 05 '13 at 15:49
Thanks @Sergeinsas, looks promising. I'm going deeper and I will post an answer asap. — axcdnt, Sep 05 '13 at 15:57

score 1 · Answer 1 · edited May 23 '17 at 10:32

I would dissociate the presentation in the spreadsheet from what you need to send in the email.

The result you get that in your column is displayed correctly, but represents the offset in milliseconds (displayed in hours) from the 1st January 1900 at 00:00:00 GMT.

So when you convert that to a date that is what you get.

Instead, that the number of milliseconds between the two dates and use a function that will return a 'human readable' number ... such as the one described here:

Convert time interval given in seconds into more human readable form

Good Luck

score 0 · Accepted Answer · answered Sep 06 '13 at 12:56

Solved my problem with a simple approach and Math.round(...) makes it quite acceptable:

function getReadableTime(ms) {

    var spreadsheetTimezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
    var dateString = Utilities.formatDate(ms, spreadsheetTimezone, 'EEE, d MMM yyyy HH:mm:ss');
    var date = new Date(dateString);

    var epoch = new Date('Dec 30, 1899 00:00:00');
    var diff = date.getTime() - epoch.getTime();

    x = diff / 1000;
    seconds = Math.round(x % 60);
    x /= 60;
    minutes = Math.round(x % 60);
    x /= 60;
    hours = Math.round(x % 24);
    x /= 24;
    days = Math.round(x);

    return createMsg(hours, minutes);
}

The function createMsg(...) is omitted because it takes the parameters and create a readable message to the user. The trick here is creating a time range in ms and follow one of the rules to make it seconds/minutes/hours/days or whatever you need.

Calculate time range and parse date

2 Answers2