Forum Discussion

maartjedutchy's avatar
maartjedutchy
Frequent Visitor
8 years ago
Solved

Convert duration (text) to minutes

Dear PowerBI rockstars,

 

I have a duration (text format) which looks like 285d 4h 8m 10s (days hours minutes and seconds) which I want to convert to total minutes. However the duration could also be 1h 13m 15s (no whole days) or 1m 54s (no whole hours or days)

Examples:

281d 12h 13m 13s 
1h 8m 38s 
59m 50s 
34s 

 

I was thinking of doing some kind split column with a lookup/conditional column in PBI, if column contains "d' then take first 2 characters... But can't really figure it out in DAX. Does anybody have an idea how to convert this duration into minutes taking the various examples into account..?

 

Thanks,

 

Maartje

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi maartjedutchy,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    Column = 
var indexOfD = SEARCH("d",Table1[Column1],1,1)
var indexOfH = SEARCH("h",Table1[Column1],1,1)
var indexOfM = SEARCH("m",Table1[Column1],1,1)
var indexOfS = SEARCH("s",Table1[Column1],1,1)
var d = VALUE(IF(indexOfD>1,MID(Table1[Column1],1,indexOfD-1),"0"))
var h = VALUE(IF(indexOfH-indexOfD=0,"0",IF(indexOfH-indexOfD>=4||indexOfH=3,MID(Table1[Column1],indexOfH-2,2),MID(Table1[Column1],indexOfH-1,1))))
var m = VALUE(IF(indexOfM-indexOfH=0,"0",IF(indexOfM-indexOfH>=4||indexOfM=3,MID(Table1[Column1],indexOfM-2,2),MID(Table1[Column1],indexOfM-1,1))))
var s = VALUE(IF(indexOfS-indexOfM=0,"0",IF(indexOfS-indexOfM>=4||indexOfS=3,MID(Table1[Column1],indexOfS-2,2),MID(Table1[Column1],indexOfS-1,1))))
return
d*3600+h*60+m+s/60

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi maartjedutchy,

     

    Based on my test, the formula below should work in your scenario. :smileyhappy:

    Column = 
var indexOfD = SEARCH("d",Table1[Column1],1,1)
var indexOfH = SEARCH("h",Table1[Column1],1,1)
var indexOfM = SEARCH("m",Table1[Column1],1,1)
var indexOfS = SEARCH("s",Table1[Column1],1,1)
var d = VALUE(IF(indexOfD>1,MID(Table1[Column1],1,indexOfD-1),"0"))
var h = VALUE(IF(indexOfH-indexOfD=0,"0",IF(indexOfH-indexOfD>=4||indexOfH=3,MID(Table1[Column1],indexOfH-2,2),MID(Table1[Column1],indexOfH-1,1))))
var m = VALUE(IF(indexOfM-indexOfH=0,"0",IF(indexOfM-indexOfH>=4||indexOfM=3,MID(Table1[Column1],indexOfM-2,2),MID(Table1[Column1],indexOfM-1,1))))
var s = VALUE(IF(indexOfS-indexOfM=0,"0",IF(indexOfS-indexOfM>=4||indexOfS=3,MID(Table1[Column1],indexOfS-2,2),MID(Table1[Column1],indexOfS-1,1))))
return
d*3600+h*60+m+s/60

     

    Regards

    • jh186046's avatar
      jh186046
      Regular Visitor
      7 years ago

      I have similar data: duration formatted as text. When I applied the solution above, I get anerror saying the "MID" function has the wrong data type or an invalid value.

       

      Any suggestions?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    None of the above worked for me. Here is my code for text in the form: 1d23h12m1s (numbers arbitrary). I also have it returning hours but that's an easy switch.

     

    Hours =
    // Days
    var indexOfD = SEARCH("d",Table[Column],1,1)
    var d = VALUE(IF(indexofD>1, MID(Table[Column],1,indexOfD-1), "0"))
    var strAfterD = IF(indexofD>1, MID(Table[Column], indexofD+1, LEN(Table[Column])-indexofD), Table[Column])
    //Hours
    var indexOfH = SEARCH("h",strAfterD,1,1)
    var h = VALUE(IF(indexofH>1, MID(strAfterD,1,indexOfH-1), "0"))
    var strAfterH = IF(indexofH>1, MID(strAfterD, indexofH+1, LEN(strAfterD)-indexofH), strAfterD)
    //Minutes
    var indexOfM = SEARCH("m",strAfterH,1,1)
    var m = VALUE(IF(indexofM>1, MID(strAfterH,1,indexOfM-1), "0"))
    var strAfterM = IF(indexofM>1, MID(strAfterH, indexofM+1, LEN(strAfterH)-indexofM), strAfterH)
    //Seconds
    var indexOfS = SEARCH("s",strAfterM,1,1)
    var s = VALUE(IF(indexofS>1, MID(strAfterM,1,indexOfS-1), "0"))
    return d*24+h+m/60+s/3600