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

Question

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!

Welcome to [Stack Overflow](https://stackoverflow.com/tour). — doubleunary, May 23 '22 at 08:56

score 0 · Accepted Answer · answered May 23 '22 at 08:56

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.

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

1 Answers1