Update custom text column calculation to time type!

[dubya]

Hello, I am attempting to convert this decimal column into an hours and minutes column. 

My formula below works well, but creates the column as a text column not a number column. 

Adjusted Hours HHMM = VAR _hrs = QUOTIENT ( '12 1/2'[Average Adjusted Hours ] , 60 )

VAR _mins = INT ( '12 1/2'[Average Adjusted Hours ] - _hrs * 60 )

VAR _sec = MOD ( '12 1/2'[Average Adjusted Hours ] , 1.0 ) * 60

RETURN

FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")  

I need to be able to sum these columns.

Thank you for your help!

[ThxAlot]

Easy enough,

[lbendlin]

If you want to be able to do math on these values then you need to keep them as is.  The format can be applied at the last second (ie as a different measure) before your display the result.

[dubya]

Thank you, 

Are you able to provide additional detail as to had to add the formula as a measure? 

[Anonymous · Accepted solution]

Hi @dubya ,

I create a table as you mentioned.

Then I think you can change your DAX code, it will give you Date/Time type. You can change format as you like.

Column = 
VAR _hrs =
    QUOTIENT ( '12 1/2'[Average Adjusted Hours], 1 )
VAR _mins =
    MOD ( '12 1/2'[Average Adjusted Hours] * 60, 60 )
RETURN
    TIME ( _hrs, _mins, 0 )

Next I think you can create another calculated column.

Total Adjusted Hours =
VAR TotalMinutes =
    SUMX ( '12 1/2', '12 1/2'[Column] * 60 )
VAR Hours =
    QUOTIENT ( TotalMinutes, 60 )
VAR Minutes =
    MOD ( TotalMinutes, 60 )
RETURN
    TIME ( Hours, Minutes, 0 )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

see the answer by @ThxAlot - even easier.