1

One cell in a Google Sheets document holds a value given by a SUM of cells formatted as "elapsed hours":"elapsed minutes". Attempting to make calculations in a custom script on this cell will throw errors. Further investigation using typeof() function will show this as "object". How can I convert the cell value to number? Attempted parseInt() or parseFloat() unsuccessfully.... In the linked sheet here you will see that:

  • cell A1 having a formula that returns 288:00 formatted as duration,

  • cell A2, having =daysHrsMins(A1), that returns a negative value, -3.179.700.519.840.000,00 from the following function:

    function daysHrsMins(input) {
   return input*1440;
}

Regional settings: Italy
Timezone settings: (GMT +1) Paris

Improve this question
5
  • 1
    Add a minimum, complete and verifiable example (add the minimum code necessary to reproduce the problem) and include the textual error messages that you get.
    – Rubén ♦
    Oct 19, 2017 at 19:37
  • See here: docs.google.com/spreadsheets/d/… - A2 will return type object and calcs are scrambled
    – Riccardo
    Oct 19, 2017 at 20:04
  • While a link to spreadsheet is helpful, questions should be self contained. Please edit the question to add the relevant information.
    – Rubén ♦
    Oct 19, 2017 at 20:20
  • Looks that the custom function is working fine. What are the errors that you get?
    – Rubén ♦
    Oct 19, 2017 at 20:29
  • Why do I get a negative value?
    – Riccardo
    Oct 19, 2017 at 20:31

1 Answer 1

Reset to default
2

Short answer

How can I convert a cell value to a number?

On Google Sheets durations are just a display format for date values, so when a custom function takes a duration it's treated as a date object.

Explanation

Doing arithmetic operations

When we are doing arithmetic operations on dates/times/duration, we should bear in mind the following:

  • Google Sheets

    • Use day as the unit
    • Use December 30, 1899 0:00:00 as the zero.
    • Doesn't handle timezones, but spreadsheets has a timezone setting.

  • Google Apps Script / JavaScript

    • Use milliseconds as the unit
    • January 1, 1970 0:00:00 in UTC as the zero.
    • Date object includes timezone.
    • The Script project has a timezone setting.

Display format

  • Google Sheets

    • Has a duration format ([h]:mm:ss.00).

  • Google Apps Script / JavaScript

    • Has the Utilities Service and formatdate method.
    • Hasn't a built-in duration format.

Transfer of dates between Google Sheets and Google Apps Script

When a date value is transferred from Google Sheets to Google Apps Script or vice versa it is automatically converted considering the timezone of the spreadsheet.

Solution

As Google Sheets and Google Apps Scripts duration/date handling is different the simplest solution is to do the conversion from date/duration to number on the origin application.

On Google Sheets we could use TO_PURE_NUMBER to do this conversion:

=daysHrsMins(TO_PURE_NUMBER(A1))

For certain use cases it could be convenient to use a JavaScript library like moment.js

References

Related Q&A from this site:

Related Q&A from Stack Overflow

Improve this answer
8
  • Thanks Ruben, I thought that the format is just applied when displaying values, without altering the cell/variable type. Is there a way to pull an "object" and convert it accordingly?
    – Riccardo
    Oct 20, 2017 at 8:14
  • I have attempted to access the cell using getHours() function, but the result is wrong. Changed the function as follows function myHours(input) { return input.getHours(); }
    – Riccardo
    Oct 20, 2017 at 14:03
  • @Riccardo: I updated my answer.
    – Rubén ♦
    Oct 20, 2017 at 15:20
  • I have changed the function to function daysHrsMins(input) { return TO_PURE_NUMBER(input); } and now I get an error: "Error ReferenceError: "TO_PURE_NUMBER" is not defined. (line 2)."
    – Riccardo
    Oct 22, 2017 at 19:16
  • @Riccardo spreadsheet built-in functions doesn't work in Google-apps-script
    – Rubén ♦
    Oct 22, 2017 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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