# Google Sheets – How to Cast 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

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