How can show duration normally?
This is an inside glide table (google sheet is not connected)
Thank you (
Gideon
Looks like you have the column formatted to a text column. I would change it to a date column formatted to display time only and see if that works.
Or maybe you’ll have to do some math on it to convert it to a time duration.
1 Like
What would you expect the result to look like? Just want to know, so I can give you the right answer.
1 Like
Thank so much for the help
10 min → 00:10:00
1 hour will look like → 01:00:00
Ten seconeds → 00:00:10
I took it a step further and included Days as well. You can exclude it if you don’t need it. It’s a little involved, but I’ll explain the best I can.
Here is my results:
First we have the Duration column, which is obvious and what you already have.
Next we have the Days column:
FLOOR(D)/100
This is simply taking the duration and dropping off the decimals.
The division by 100 will be explained later.
Next we have the Hours column:
FLOOR(MOD(D,1)*24)/100
This takes the duration, removes everything to the left of the decimal, then we multiply the decimal by the number of hours in a day (24) to get the equivalent of the decimal remainder in total hours. The FLOOR removes any hours, minutes, and seconds that would be contained in the decimal remainder.
The division by 100 will be explained later.
Next we have the Minutes column:
FLOOR(MOD((D*1440)/60,1)*60)/100
This takes the duration, multiplies by the number of minutes in a day (1440) to get the equivalent of the number of minutes in that decimal remainder, then divides by 60 to get the number of minutes in an hour. Then we take the decimal remainder of that and apply the FLOOR to remove any decimal seconds. Then we multiply the decimal by the number of minutes in an hour (60) to get the number of remainder minutes.
The division by 100 will be explained later.
Next we have the Seconds column:
FLOOR(MOD((D*86400)/60,1)*60)/100
This takes the duration, multiplies by the number of seconds in a day (86400) to get the equivalent of the number of seconds in that decimal remainder, then divides by 60 to get the number of seconds in an minute. Then we take the decimal remainder of that and apply the FLOOR to remove any decimal sub-seconds. Then we multiply the decimal by the number of seconds in an minute (60) to get the number of remainder seconds.
The division by 100 will be explained later.
Next we have a Template column to join the Days:Hours:Minutes:Seconds together:
Next we have a Text column:
This is just an empty column so we can use it as a replacement value below.
Finally we have a final Template column:
This takes the previous template column and replaces 0. with the blank value.
The reason we divided everything by 100 is so we can do these replacements and still retain the leading zero in from of all the numbers. The replacement only removes the zero and the decimal.
We also have a replacement to replace the . with the blank value. This is only there if the total number of days is greater or equal to 100. In this case we could end up with a value of 1.00 and we only want to remove the decimal in this case.
Hopefully this all works. If not, let me know with a duration that’s not showing correctly and I can double check if I need to alter the formulas. I realize my explanation may be more confusing than the actual formulas, but it can be hard to explain.
11 Likes
Thank you so much, I did not think it needs so much work around.
Thank you (
Thank you very much just one more small thing…
How to show it in the chart? currently it shows only seconeds
Anyone may know how to solve this last issue?..