Forum Discussion

simon_70's avatar
simon_70
Copper Contributor
Jan 05, 2023

Excel - PT0S - ISO 8601

Hey All

 

Sorry anther basic question again sorry I am trying to convert this column "Video Duration" it has the cells as PT0S .

 

We have exported teams messages in our Origination report which came to me like in this format looking at this it is in a ISO 8601 format any ideas how I can get to to read in human English haha 

 

excel
Excel for web
Formulas and Functions
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 05, 2023

    simon_70 

    For audio duration:

     

    =IFS(ISNUMBER(FIND("H",I2)),"",ISNUMBER(FIND("M",I2)),"0:",TRUE,"0:0:")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"PT",""),"H",":"),"M",":"),"S","")

     

    Fill one cell to the right for video duration. Then fill down.

    • simon_70's avatar
      simon_70
      Copper Contributor
      Jan 06, 2023

      HansVogelaar 

       

      hey thank you so much the only issue is we have "P1DT9" in the cells 

      im putting this onto a Powerbi so it need to be a readable format i have tried formatting the column but nothing changes   

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 06, 2023

        simon_70 

        The screenshot in your first post had only PT examples, and none with P1D, so I didn't take that into account.

        P1DT7H19M26S means 1 day, 7 hours 19 minutes and 26 seconds.

        What should the output of the formula for such a value look like?

Resources