Forum Discussion
Working With Date / Time Formats
Hi, likely a basic question here.
I have a table in excel that I would like to link to Powerbi. The figures in the table are a result of subtracting one date and time away from another to give me a duration. To then put that in to hh:mm in excel that is formatted in excel to a custom format [h]:mm:ss.
When I import this into BI in then shows it as a date / time the same as excel does when you click in the cell. How can i get PowerBI to show the formatted figures?
Thanks in advance.
Hi shastie
you can get it with a calculated column in DAX like this:
Formatted = VAR _DaysInHours = INT('Table'[DateTime]) * 24 VAR _Hours = INT(MOD('Table'[DateTime],1) * 24) VAR _Minutes = FORMAT(MOD('Table'[DateTime],1),"nn:ss") RETURN _DaysInHours + _Hours & ":" & _MinutesWith kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
4 Replies
Hi shastie
you can get it with a calculated column in DAX like this:
Formatted = VAR _DaysInHours = INT('Table'[DateTime]) * 24 VAR _Hours = INT(MOD('Table'[DateTime],1) * 24) VAR _Minutes = FORMAT(MOD('Table'[DateTime],1),"nn:ss") RETURN _DaysInHours + _Hours & ":" & _MinutesWith kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
