Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert Decimal Duration in HH:MM:SS format

Greentings, Power BI friends!   A got little problem and I'm asking for help because I couldn't find a solution among the forum posts.   First, I have this table with some dates, hours, minutes a...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    You could try something like this:

    Calculated column:

    Seconds = DATEDIFF(CreationDate, ResolutionDate, SECOND)

     


    Calculated column:

    Your_Time =
    IF([Seconds] <> BLANK();
    VAR Duration = [Seconds]
    VAR hh =
    INT ( Duration / 3600)
    VAR Minutes =
    INT ( MOD( Duration - ( hh * 3600 );3600 ) / 60)
    VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( hh * 3600 );3600 ); 60 );0) // We round up here to get a whole number
    VAR H =
    IF ( LEN ( hh ) = 1;
    CONCATENATE ( "0"; hh );
    CONCATENATE ( ""; hh )
    )
    VAR M =
    IF (
    LEN ( Minutes ) = 1;
    CONCATENATE ( "0"; Minutes );
    CONCATENATE ( ""; Minutes )
    )
    VAR S =
    IF (
    LEN ( Seconds ) = 1;
    CONCATENATE ( "0"; Seconds );
    CONCATENATE ( ""; Seconds )
    )
    RETURN
    CONCATENATE (
    H;
    CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )
    ); BLANK())
Anonymous's avatar
Anonymous
Not applicable
6 years ago

AWESOME! Thank you very much!

 

Visually is great, but any advices in how I can make math operations with this column?

Anonymous's avatar
Anonymous
Not applicable
6 years ago
I think it could be better if you do your math operations working with seconds. After that you'll use this formula.

If it helped, please mark my previous reply as 'accepted answer', thanks!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes, I think will be better, thanks for the help.