Convert in a Decimal Value into a Duration 000:00:00 when Column Type is 'Decimal'

[Tony_Insua]

I have a column that includes values like 30.6, 4000, 600, etc. so the column type is set to Decimal. There is however, some values that are listed as 0.00723423 or 0.0052342 which actually denote a DURATION. How do you list these these as durations without changing the column type to DURATION (as it wold change the other values which are correctly listed)

See below example highlighted in green. This should be a DURATION, but notice the previous number is listed correctly.

I feel like this would be an IF expression. IF the value is less than 0.1, than do this calculation to covert it to a duration, ELSE, leave the value unchanged.

What do you think?

[Steve · Accepted solution]

There is no way to display a Decimal value as a Duration directly.

Instead of displaying the column you currently are, add and instead display a virtual column that renders the other column’s value in the desired way.

[Tony_Insua]

Hi Steve,
How would I then be able to possibly show two columns in one space? I’m using the CARD VIEW, I’ve selected the “PEAK METRIC HEADER” which concatenates TWO columns to show the value and unit (ie 160 KM for example). There’s only one slot here to pick the header so if I created a virtual column for just the duration piece I wouldn’t be able to show both the PEAK METRIC HEADER and the DURATION (virtual column) header.

[Tony_Insua]

Thanks for the advice. I’ll give this a try. As always, I appreciate your help.

[Bahbus]

If you want to do that, make that new VC actually a TEXT column and
CONCATENATE([PEAK METRIC HEADER], " - ", your duration calculation)

[Tony_Insua]

Hi Bahbus, how do you convert the decimal number into a duration per your “duration calculation”? Here are examples of the decimals being displayed in my app. Thanks!

[Bahbus]

The conversion will depend on you. I don’t know what that decimal represents or how you got to it. 0.007337963 hours? days? Either way, you’ll need to multiply it by something to get it to be representative of what you want.

[Tony_Insua]

So a duration represented like this 0:09:10

[Tony_Insua]

So for example a number like 0.0063657 is the equivalent to 0:09:10 as a duration.

[Steve]

Tony_Insua:

So for example a number like 0.0063657 is the equivalent to 0:09:10 as a duration.

How did the 0.0063657 value get stored in the spreadsheet? From the app? If so, how was the value input?

[Tony_Insua]

This value is from a table in google sheets. The 0.0063657 figure is a number format; however, when converted into a duration in google sheets it becomes the 0:09:10 duration format.

[Steve]

In Google’s duration format, or AppSheet’s?

[Tony_Insua]

Google’s

[Steve]

I don’t know how to convert Google’s representation of a duration to AppSheet’s.

Ideally, all data in your spreadsheet should be formatted as plain text, and its appearance should be consistent with examples you see in the AppSheet help docs.

I’d guess 0.0063657 is the percentage of seconds in a day. If so, you’d have to do the math to calculate seconds, minutes, and hours, then construct the Duration value.