Converting Whole Number to Time

[Anonymous]

Hi All,

I have this data of Transaction Time in my table and it shows the time as a whole number. E.g. 23:00 is shown as 2300. How can I convert it into time format? I tried transforming it but it returned all the values as 12:00. Your inputs will be highly appreciated. Thank you so much! 

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you should be able to use the formula(DAX) below to create a new calculate column to convert your original column to time format. 

Column =
IF (
    LEN ( Table2[TR_TIME] ) = 4,
    LEFT ( Table2[TR_TIME], 2 ) & ":"
        & RIGHT ( Table2[TR_TIME], 2 ),
    LEFT ( Table2[TR_TIME], 1 ) & ":"
        & RIGHT ( Table2[TR_TIME], 2 )
)

Regards

[Anonymous]

hi,

i have a lot of stats on times that are in csv format that i have imported to power bi. the formula above  works to a certain extent. besides the 3- or 4-digit times (example 115 representing 1:15 or 1615 representing 16:15 that seem seem to work using above formula,  there are times that are just 1 or 2 digits (example 7 which represents 00:07 or 22 that represents 00:22) that may require modification to the formula. kindly advise on how to handle by dax formula for those timing numbers ranging from 1-4 digits.

tks, -nik

[Anonymous]

i found the solution in creating a calculated column (PLN_DEP_TIME) for the column (CRS_DEP_TIME) from the '2017'-table contains those 1-4 digit times as follows. the resultant column wil subsequently b set to hh.nn time-format:

PLN_DEP_TIME =

IF ( LEN ( '2017'[CRS_DEP_TIME] ) = 4,

LEFT ( '2017'[CRS_DEP_TIME], 2 ) & ":" & RIGHT ( '2017'[CRS_DEP_TIME], 2 ),

IF ( LEN ( '2017'[CRS_DEP_TIME] ) = 3,

LEFT ( '2017'[CRS_DEP_TIME], 1 ) & ":" & RIGHT ( '2017'[CRS_DEP_TIME], 2 ),

IF ( LEN ( '2017'[CRS_DEP_TIME] ) = 2,

"0:" & RIGHT ( '2017'[CRS_DEP_TIME], 2 ),

"0:0" & RIGHT ( '2017'[CRS_DEP_TIME], 1 )

)))

[Anonymous]

@v-ljerr-msft thank you so much! 

[Anonymous]

Generally Time is held as a decimal number between 0 and 1.  You'll firstly need to convert your time to ensure that its not HHMM (i.e. if 13:50 is stored as 1350, you'll need to change it to be stored as 1383).  Once you have done that, you can divide the number by 2400