How to convert HH:MM:SS to just HH:MM?

wow… my App is working for non accepted browser settings LOL

anyway… just use the column to convert the date… in any format

I think this could be tricky, mainly because you are using a glide chart and the chart is displaying the duration which is a decimal number with a friendly format on top of it. Since it’s still a number, the chart will properly sum the decimal numbers for you, but still display the friendly duration format.

I can use a javascript column remove seconds and reformat, but it’s essentially a string instead of a number, so the chart won’t be able to properly sum the durations while retaining the format.

image

It looks good in the data, but doesn’t translate well in the chart.

Unfortunately, I can’t really think of good way to do this. The native duration formatting is applied on top of the underlying decimal number, for display purposes, post calculation. We are trying to apply our own formatting before the chart calculations are performed, which breaks the underlying numeric value. Because of that the chart will not work correctly. I think Glide would have to allow for an option on the duration column to show or hide seconds which would then apply after the chart has done it’s calculations.

2 Likes

Sorry, it was late last night when I posted that and I was on my way to bed, so I didn’t have time to elaborate. But if you’d read the post I linked to, you’d have seen that code column accepts an integer value as input (number of minutes).

So to use it and get the correct results, you’d first need to convert your duration into a number of minutes with a Math column. Or if your current duration is the result of a math column, adjust the formula to output integer minutes instead of a duration - eg. Round((End - Start)*1440). Then the code column will give you the correct results.

That said, having now read the rest of the responses in the thread, it’s still not going to help for your actual use case, because it will be returning a string.

Personally, I’d use quickchart. Then you can put whatever you want on the chart, anywhere on the chart.

3 Likes

Thanks @Jeff_Hager for taking out time to experiment this and thanks @Darren_Murphy for the code. Quickchart was my last option since I wanted to do something native on Glide. That’s unfortunate that this can’t be done natively on Glide :frowning:

But lets see what @Uzo has in his mind since he is confident enough it can still be achieved through Glide :smiley:

1 Like

I suspect that Uzo was referring to the Format Date plugin. Two points I’d make about that:

  • It won’t help you in this case because it returns a string. And so you will be back to square one.
  • Use it at your own risk
3 Likes

here you go… no problem :wink:

you guys are overthinking this problem… LOL

Historically, whenever I have to do any formatting or math with dates and time, I’ve found it easiest to make the table in Google Sheets and do the calculations and conversions on the Google side of things. There are a lot more functions giving much more control over what Glide can do with dates and time natively. I haven’t tested Glide since the recent updates around time zones, but from what I’ve seen in this thread it looks like Glide still has room to grow.

yep… you are absolutely right! but… it takes time to sync with GS and if you are using only Glide tables, a little bit of brain gymnastics will solve all problems :wink:

I’ll admit that it took me a very long time to get my head around working with dates and times in Glide, but now that I’m relatively comfortable with it I can’t possibly imagine a scenario where I’d ever try and do any sort of date/time manipulation or formatting in a Google Sheet.

But each to their own :slight_smile:

That looks like exactly what I want. How’d you configure the “Convert Duration” Column?

just don’t use it for other calculations in the app… only for displaying

1 Like

The big one that I haven’t taken the time to recreate the math within Glide is the “DATEDIF” function. The “YD”, “M” and “D” unit variables that do the leap year math for me is great. I’ve posted this elsewhere in more detail, but the summary is that while I COULD do the math and recreate in a Glide table, it is just easier to use the Google function.

I know this. Could you show the “CONVERT DURATION” settings?

Just put that one… that’s all it is… duration/60

did you make it work?

Well that’s interesting, and really really useful.

I’m not sure I can explain how or why that works - can you? :thinking:

yes… is very simple, is like moving a decimal point in numbers, when you divide or multiply by 10 ;-)… so hours and minutes are counted to 60… when you divide by 60, it will move hours to minutes and minutes to seconds

1 Like

ah-ha!

I see what’s going on. So in actual fact, that’s not the true duration. We’ve just converted 32 hours and 30 minutes to 32 minutes and 32 seconds.

okay, so maybe not so useful after all. A little bit of smoke and mirrors

yes, that’s why I made a note for him, to use only for display purposes… so you can have two columns… one with a real duration for calculating… and one for displaying

1 Like