Transform time from text to time format

[AlePen]

Hi everyone,

I have time data in the following format:

12d 8h 34m 12s

1h 3m 3s

23m 2s

15s

The data are recognized as text and I want to transform them in time format data.

How can i do?

thanks in advance

[v-xicai · Accepted solution]

Hi @AlePen 

Your original time data is count period time specially having "12d" element is beyond the TIME elements . Which Time format value do you want to display if the original value contains day part (eg: 12d 8h 34m 12s)?

You can create columns to meet your demand, assuming transfer it to time format "HH:mm:ss" like picture below.

Time data = RIGHT(Table1[Time original],LEN(Table1[Time original])-IF(IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0))>0,IF(FIND("d",Table1[Time original],1,0)-1<0,0,FIND("d",Table1[Time original],1,0)+1)))

ML = IF(FIND("m",Table1[Time data],1,0)-1<0,0,FIND("m",Table1[Time data],1,0)-1)

HL_M = IF(FIND("h",Table1[Time data],1,0)=0,Table1[ML]-FIND("h",Table1[Time data],1,0),Table1[ML]-FIND("h",Table1[Time data],1,0)-1)

SL = IF(FIND("s",Table1[Time data],1,0)-1<0,0,FIND("s",Table1[Time data],1,0)-1)

ML_S = IF(FIND("m",Table1[Time data],1,0)=0,Table1[SL]-FIND("m",Table1[Time data],1,0),Table1[SL]-FIND("m",Table1[Time data],1,0)-1)

Hour = IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=0,"00",IF(LEN(LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1)))=1,CONCATENATE("0",LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))),LEFT(Table1[Time data],IF(FIND("h",Table1[Time data],1,0)-1<0,0,FIND("h",Table1[Time data],1,0)-1))))

Minute = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])),RIGHT(LEFT('Table1'[Time data],Table1[ML]),Table1[HL_M])))

Second = IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=0,"00",IF(LEN(RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S]))=1,CONCATENATE("0",RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])),RIGHT(LEFT('Table1'[Time data],Table1[SL]),Table1[ML_S])))

Time Text = CONCATENATE( CONCATENATE(CONCATENATE(CONCATENATE(Table1[Hour],":"),Table1[Minute]),":"),Table1[Second])

Time Format = TIMEVALUE(Table1[Time Text])

As I don’t know how to treat the value which contains the day part, the final Time Format column doesn’t consider the day part value.

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXB2rDPFDrxImIWimt...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.