How to convert Duration 00:00:00 into "0 h 00 min"

What is the virtual column expression to convert Duration column of 00:00:00 into “0 h 00 min” removing the seconds.
Thanks!

@Tony_Insua
If you expand the Virtual Column’s details, you will notice the Ignore Seconds setting to turn it off.

1 Like

Also:

1 Like

@LeventK thanks. I’m looking to keep the seconds to show 00:00:00 format in the detail view only; however, in my card view, I would like to display “0h 00m” this way which I believe is a separate VC expression to convert my DURATION column.

@Steve I’m having issues with the expression as I don’t see it the “0h 00m” example on this help guide.

I’ve tried:

TEXT([DURATION], “H:M”), using the [DURATION] column for when.

Result: something went wrong:
Unable to cast object of type ‘System.TimeSpan’ to type ‘System.IConvertible’.

As mentioned in the article on TEXT() shared by @Steve, I believe the TEXT() works on Date , DateTime , or Time

You may need to convert the [Duration] to time value. Please try with

TEXT(TIME([Duration]), “HH:MM”)

Another option could be

CONCATENATE(HOUR([Duration]), " h", MINUTE([Duration]), " min")

4 Likes

Thanks @Suvrutt_Gurjar

The final expression I landed on was this. Always a big help.

IF(
HOUR([DURATION])=0,
CONCATENATE(MINUTE([DURATION]), " min"),

IF(
HOUR([DURATION])>0,
CONCATENATE(HOUR([DURATION]), "h “, MINUTE([DURATION]), " min”), “”)

)

2 Likes

Excellent.

1 Like

Similar:

CONCATENATE(
  IFS(
    (HOUR([Duration]) > 0),
      (HOUR([Duration]) & "h ")
  ),
  (MINUTE([Duration]) & " min")
)
2 Likes