How to convert decimal to duration?

How do i convert a decimal to a duration format?

Do you mean that you have a number representing decimal hours (eg. 5.3) and you want to represent it as hh:mm?

First thing to note is that if you use a Glide math column to subtract one date/time from another, then it will give you a duration format by default. So this is the easiest and most direct way to get there.

But if you’re not starting with two dates and only have a decimal number, then you can convert it with math columns to separately extract the HH/MM components, and then stitch them together with a template column.

To get the hours: Trunc(Round(Hours,2)) (where Hours is your decimal hours)
To get the minutes: Round((Hours-WholeHours)*600/10,0) (where WholeHours is the result of the first column)

2 Likes

Wow support is quick.

Will this work if I am using a Math formula to calculate the decimal duration?

Thank you I got it to work. How would I calculate the seconds?

The final hurdle is to add the duration to the DateTime? I am not sure how to do that. Any help would be appreciated.

To get seconds as well, firstly adjust the formula for minutes as follows:

Trunc((Hours-WholeHours)*600/10,0) (uses trunc instead of round, so we don’t discard the remainder)

And then use the following to get the remaining seconds:

(Round((Hours-WholeHours)*600/10,2)-Minutes)*60

To add decimal hours to a date/time, it’s just a simple math formula: Date + (Hours / 24)

2 Likes

Perfect, I have it working correctly. Thank you Darren.

Can you confirm if the formula for seconds is correct? I am getting a zero value. I assume I add the seconds formula to a new math column?

hmm, I might have messed that up. Let me double check and I’ll get back to you.

Thanks

It gives the correct results for me.
One problem you’ll have is if you want leading zeroes - eg. 1:03:05 instead of 1:3:5, then you’ll need extra columns to apply that formatting.

To be honest, this is a case where I would probably turn to a single Javascript column and save all that mucking about. Try the following in a JS Code column:

var d = new Date(0,0);
d.setSeconds(p1 * 60 * 60);
return d.toTimeString().slice(0,8);

Screen Shot 2022-06-10 at 7.26.25 PM

I have tried that but get an Invalid Column Value

Nevermind, figured out the issue. It’s working now.

yeah, you would get that if the Hours column is empty.
Simple fix is to insert a bit of code to short circuit when it’s not defined, something like this:

if (p1 === undefined) { return undefined }

Sorry I know this is off topic but is it normal for the Set Column Values to not add all the data to a new row in Google Sheets when you add new data but only when you edit and then click done?

I’m not sure I understand your question. Set Column values isn’t required when you edit a record. Any changes that you make should be saved once you click on done. You should see the changes in the underlying Glide Table immediately, but it may take a short while before the change appears in a connected Google Sheet. Does that answer the question?

I have a trip cost screen, when I hit the + to add a new record, I use the Set Column Values to set the column Values of my google sheet from glide’s computed columns. But that does not happen. The column values are left blank after adding. When I click to edit the new row I have to use use Set column values as a workaround to write the missing column values back to my Google Sheets.

The data is never written to the columns on add no matter how long a wait.

This issue appears to be around since 2020, after going through a few forum posts.


ah, I see.
My guess would be that you might have a chicken and egg situation. That is, the Set Column Values happens before the values are calculated. This might work better with a Custom Form.

Curious about why you need these values in the Google Sheet? Are they used outside your app?

Hola,

Maybe a little late but it can be useful in future.

What if we use the Duration column that does it automatically (here thinking about saving columns/data) :innocent: ?

image

Just:

  1. Take the time as number
  2. Convert it to hours (time/24)
  3. Write result (time/24) to Duration column
  4. Enjoy Glide

image

Saludos!

3 Likes

Hi, thanks for the feedback. How do you write the Time to a Duration Column?