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 ...
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

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?