Help with correctly converting ISO 8601 data into Airtable's "Duration" field?

[theecarls]

Ok, not sure I wrote the title of this topic clearly so here’s an explanation.

I have “duration” data imported into Airtable in an ISO 8601 format (just into a normal long text field, more on that below). An example of this format is PT1H30M15S which is equal to 1 Hour 30 Minutes and 15 Seconds of time. PT stands for “Period of Time” for those curious.

Problem:
I am trying to get the ISO 8601 formatted data into airtable’s “Duration” field but the formatting does not seem to be 1:1. Using the above example, pasting “PT1H30M15S” into an airtable Duration field gives 3:36:55 instead of the expected 1:30:15. I assume this is partially why I had to import this data into a long text field and couldn’t get it to work out the box with the duration field.

Can anyone help me figure out how to properly convert my data into Airtable’s duration field format? I have a feeling it’s going to require a formula so that’s why I posted here under this category, but if not my apologies.

[kuovonne · Accepted solution]

A duration field stores the underlying value as a number of seconds.

Here is a formula that converts the text string you provided into a number of seconds. You can set the formatting options for the formula field to display the result as a duration.

Note that I only tested this with the one text string that you provided. If your ISO 8601 duration might have years, months, weeks, or days, this formula will not work. If the duration might be negative, this will not work.

SUM(
    IF(
        REGEX_MATCH({DurationText}, "\\d+H"),
        VALUE(
            REGEX_EXTRACT(
                REGEX_EXTRACT(
                    {DurationText},
                    "\\d+H"
                ),
            "\\d+"
            )
        )
    ) * 3600,
    IF(
        REGEX_MATCH({DurationText}, "\\d+M"),
        VALUE(
            REGEX_EXTRACT(
                REGEX_EXTRACT(
                    {DurationText},
                    "\\d+M"
                ),
            "\\d+"
            )
        )
    ) * 60,
    IF(
        REGEX_MATCH({DurationText}, "\\d+S"),
        VALUE(
            REGEX_EXTRACT(
                REGEX_EXTRACT(
                    {DurationText},
                    "\\d+S"
                ),
            "\\d+"
            )
        )
    )
)

[theecarls]

This worked perfectly, thank you so much!