Parse duration format from PT0S or PT1H57M4S to 01:57:04 in Google Sheets/DataStudio-CodePudding

I'm exporting duration from clockify to google sheets, but keep getting duration in format unreadable by datastudio where I want to visualize data synced from clickify to sheets.

I tried sulution from below posts, but none of these works for me. Google Sheet formula to convert Youtube's API ISO 8601 duration format

Can you help me create a formula that would convert duration format from PT0S or PT1H57M4S to 01:57:04?

If you can please do it in this sheet, where I'm working in https://docs.google.com/spreadsheets/d/1qPIUp2q9X3Wh7V1KMVgPeqCjF_r4d5wbjlzOTTUbGZ0/edit?usp=sharing

Thanks before!

CodePudding user response：

Use regexextract(), like this:

=arrayformula( 
  iferror( 1 / ( 
    iferror( regexextract(C1:C; "(\d )H") / 24 )   
    iferror( regexextract(C1:C; "(\d )M") / 24 / 60 )   
    iferror( regexextract(C1:C; "(\d )S") / 24 / 60 / 60 ) 
  ) ^ -1 ) 
)

Format the result column as Format > Number > Duration. Note that durations of zero length are shown as blanks rather than 0.

See this answer for an explanation of how date and time values work in spreadsheets.