Formating time value

[Anonymous]

Hi everyone!

I'm looking for a way to convert whole number to time format like this: 

900→9:00

0→0:00

[Anonymous]

Thank you soooo much!!!

[amitchandak]

@Anonymous ,

new column = time(quotient([column],100),mod([colum],100),0)

[edhans · Accepted solution]

If you want to use Power Query, you will do this.

Time.From(
 Text.From(
  Number.IntegerDivide([Column1],100)
  ) 
 & ":" & 
 Text.End(
  Text.From([Column1]),
  2)
 )

It will be converted to a legitimate time of 9am assuming that 900 is the integer in Column1

The following in DAX will work on a calculated column.

Column =
TIME( ROUND(
    [Column1] / 100,
    0
), VALUE(
    RIGHT(
        FORMAT(
            [Column1],
            "#"
        ),
        2
    )
), 0 )

None of these solutions assume that the last two digits are always 00. So 945 would be converted to 9:45 both times.