Parse Duration Text String

Goal

You have a duration in a text string and want to convert it to an Airtable duration .

The Formula

IF(
    {Duration String},
    SUM(
        IF( "hours to seconds",
            IF(
                REGEX_MATCH( {Duration String}, "\\d+h" ),
                VALUE( REGEX_EXTRACT( {Duration String}, "\\d+h" ) ) * 3600,
                0
            )
        ),
        IF( "minutes to seconds",
            IF(
                REGEX_MATCH( {Duration String}, "\\d+min" ),
                VALUE( REGEX_EXTRACT( {Duration String}, "\\d+min" ) ) * 60,
                0
            )
        ),
        IF( "seconds",
            IF(
                REGEX_MATCH( {Duration String}, "\\d+sec" ),
                VALUE( REGEX_EXTRACT( {Duration String}, "\\d+sec" ) ),
                0
            )
        )
    )
)

Displaying the Result

To see the result as a duration, be sure to change the formatting options for the formula field to use the Duration format.

How it Works

The formula uses regular expressions to parse out the hours, minutes, and seconds. If the field doesn't have a particular unit, it returns zero.

Then the formula uses VALUE to convert the number string to an actual number.

Hours and minutes are converted to seconds, as Airtable durations are internally stored as seconds.

Finally, the number of seconds in the hours, minutes, and seconds are all added up with SUM

Note that the IF statements with a text string are simply comments to make the formula easier to understand.

Adapting the Formula

If your duration string will have different indicators for the hours, minutes, and seconds, you can adjust the patterns in the regular expressions

Demo Base