# Google Sheets casting cell value from Object to Number

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

• 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. Commented Oct 19, 2017 at 19:37
• See here: docs.google.com/spreadsheets/d/… - A2 will return type object and calcs are scrambled Commented 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. Commented Oct 19, 2017 at 20:20
• Looks that the custom function is working fine. What are the errors that you get? Commented Oct 19, 2017 at 20:29
• Why do I get a negative value? Commented Oct 19, 2017 at 20:31

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:

• 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

• 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.

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

• 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? Commented 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(); }` Commented Oct 20, 2017 at 14:03
• @Riccardo: I updated my answer. Commented 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)."` Commented Oct 22, 2017 at 19:16
• @Riccardo spreadsheet built-in functions doesn't work in Google-apps-script Commented Oct 22, 2017 at 19:27