Format duration in minutes as hh:mm

[Darren_Murphy]

This is a classic example of where a single experimental code column can save you a bunch of Glide computed columns.

My use case:

I have a column where the value in each row represents a duration in minutes, and I need to format that for display as hh:mm. So, for example…

• 2914 => 48:34
• 804 => 13:24
• 63 => 01:03
• 5 => 00:05
• 0 => blank

As far as I could tell, none of the plugin columns will handle this. But it looks easy, right?
Well, the best I could do with Glide computed columns was 6 (!!) columns
If anyone - @Jeff_Hager - can improve on that, please let me know. It went like this:

• Column 1: Math column to get the number of whole hours. Floor(total/60)
• Column 2: If-then-else column to zero pad the hours…
  – If hours < 10 then 0, else blank
• Column 3: Math column to get the remaining minutes. Mod(total,60)
• Column 4: If-then-else column to zero pad the minutes…
  – If mins < 10 then 0, else blank
• Column 5: Template column to stitch them together… {hh-padding}{hh}:{min-padding}{min}
• Column 6: If-then-else column to deal with the case where the total minutes is zero or empty…
  – If total is empty, then empty
  – If total equals 0, then empty
  – Else template column

Which looked like:

If it only had to be done once, then it’s not so bad. But for my use case I had to do this about 20 times After the 3rd or 4th time, I’d had enough. So decided to swap those 6 columns out for a single experimental code column…

https://mcdarren.github.io/glide-mins-to-hhmm

window.function = function (total) {
  if (total.value === undefined) return undefined;
  if (total.value === 0) return undefined;
  var hours = Math.floor(total.value/60);
  var mins = total.value % 60;
  return hours.toString().padStart(2, '0') + ":" + mins.toString().padStart(2, '0');
}

Which results in:

So that’s 5x20=100 computed columns saved with just a few lines of javascript

[Jeff_Hager]

This had been my previous attempt from a while back. Definitely not elegant by any means. Your javascript solution makes way more sense.

Time duration not presenting correctly in glide chart

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: [image] First we have the Duration column, which is obvious and what you already have. Next we have the Days column: [image] 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: [image] FLOOR(MOD(D,1)*24)/100 Th…

[ThinhDinh]

Thanks for sharing!

[nathanaelb]

What an elegant example that shows how a few lines of code can achieve more than no-code. Or maybe the same, but more efficiently.

I still prefer your 6-column no-code solution, simply because it is accessible to me, whereas there is no way I would have been able to reproduce your code.

[Darren_Murphy]

@nathanaelb

I still prefer your 6-column no-code solution,

yeah, I get that.
I’m the same - I always prefer to get things done with standard computed columns where ever I can.
But creating these same 6 columns over and over and over again was driving me absolute bonkers

I guess the nice thing about the Experimental Code column is that it becomes an extra bow for your arrow that you can pull out and use when you need it

[Robert_Petitto]

I agree… Nocode is my preferred solution but sometimes a singular hyperformula or extract matching text plug-in column is more efficient.

[ThinhDinh]

@Darren_Murphy

But creating these same 6 columns over and over and over again was driving me absolute bonkers

I agree, in some cases, it’s just more efficient. I have even created a macOS shortcut to access my Github URLs and quickly copy them to the clipboard for the experimental column. I love it.

[Darren_Murphy]

oooh, please share?

[ThinhDinh]

https://www.icloud.com/shortcuts/369ace3b767942a0881c2790855cf928

Let me know if you can access this link.

You can substitute your user name in place of mine (thinhdinhlca) and it should work well. I use it to directly grab my Pages URL instead of having to go to the specific repo to get the URL.