Forum Discussion
Converting Whole Number to Time
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!
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. :smileyhappy:
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
5 Replies
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. :smileyhappy:
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
v-ljerr-msft thank you so much!
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
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 ))))