Forum Discussion
Abdel_Spateof
7 years agoFrequent Visitor
Duration format
Hello Community This is my first time I post here :) I have a problem, I have a column with duration formatting as follows 9h 53m 38s and I want to convert it to HH:MM (09:53) How can ...
- 7 years ago
Abdel_Spateof, in response to your message, perhaps create DAX columns like this:
Hours = VAR __h = FIND("h",[Time],,BLANK()) VAR __final = IF(NOT(ISBLANK(__h)),__h-1,BLANK()) RETURN IF(NOT(ISBLANK(__final)),LEFT([Time],__final),BLANK()) Minutes = VAR __h = FIND("h",[Time],,BLANK()) VAR __finalh = IF(NOT(ISBLANK(__h)),__h-1,BLANK()) VAR __m = FIND("m",[Time],,BLANK()) VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK()) RETURN IF(ISBLANK(__finalm),BLANK(),IF(ISBLANK(__h),LEFT([Time],__finalm),MID([Time],__finalh+3,__finalm-__finalh-2))) Seconds = VAR __m = FIND("m",[Time],,BLANK()) VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK()) VAR __s = FIND("s",[Time],,BLANK()) VAR __finals = IF(NOT(ISBLANK(__s)),__s-1,BLANK()) RETURN IF(ISBLANK(__finals),BLANK(),IF(ISBLANK(__m),LEFT([Time],__finals),MID([Time],__finalm+3,__finals-__finalm-2)))After that, you can just concatenate them together as needed.
Greg_Deckler
7 years agoCommunity Champion
Abdel_Spateof, in response to your message, perhaps create DAX columns like this:
Hours =
VAR __h = FIND("h",[Time],,BLANK())
VAR __final = IF(NOT(ISBLANK(__h)),__h-1,BLANK())
RETURN
IF(NOT(ISBLANK(__final)),LEFT([Time],__final),BLANK())
Minutes =
VAR __h = FIND("h",[Time],,BLANK())
VAR __finalh = IF(NOT(ISBLANK(__h)),__h-1,BLANK())
VAR __m = FIND("m",[Time],,BLANK())
VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK())
RETURN
IF(ISBLANK(__finalm),BLANK(),IF(ISBLANK(__h),LEFT([Time],__finalm),MID([Time],__finalh+3,__finalm-__finalh-2)))
Seconds =
VAR __m = FIND("m",[Time],,BLANK())
VAR __finalm = IF(NOT(ISBLANK(__m)),__m-1,BLANK())
VAR __s = FIND("s",[Time],,BLANK())
VAR __finals = IF(NOT(ISBLANK(__s)),__s-1,BLANK())
RETURN
IF(ISBLANK(__finals),BLANK(),IF(ISBLANK(__m),LEFT([Time],__finals),MID([Time],__finalm+3,__finals-__finalm-2)))
After that, you can just concatenate them together as needed.
Abdel_Spateof
7 years agoFrequent Visitor
Awesome
Thanks Greg_Deckler