How do I return a string but have my function store a number value?

Question

The input is a number and I want the function store the value. When I use it in google sheets, I want to be able to retrieve the value from the cell.

function getTime(input) {
  n = Math.abs(input)
  m = parseInt(n)
  x = n-m
  s = Math.round(x * 60)
  return m +" m, " + s + " s"
  }

The above is a custom function for Google Sheets. I have time data that's in minutes that I just want to convert to minutes and seconds, so that the cell reads 1 m, 30 s, but I still want it to have the original number value of 1.5.

Answer 1

Considering that you are using Google Sheets, I think that you are using wrong approach as this app as well other spreadsheet applications, have a built-in way to store and display "time" values.

Instead of returning a text, return a date-time value

i.e. 1.5 minutes, should be returned as 12/30/1899 0:01:30 or 0.00104166666666667. This because Google Sheets store date, time, date-time and duration values as serialized dates. For details see https://developers.google.com/sheets/api/guides/formats.

To display it as 1 m, 30 s use the format cell number format m " m, " s " s".

1. Select the cell or range to apply the format
2. In the Google Sheets web app toolbar, click Format > Number > Custom number format
3. Write m " m, " s " s" and click Apply

Then the custom function to be used is

function getTime(input) {
  return input / 60 / 24
}