+3I have a column which automatically brings ‘time duration’ information from RescueTime through IFTTT which has a string format such as ‘3h 35min’ or ‘28min 52sec’. Since the information is saved as a string format, I would like to create a new column which converts this string into duration format.
AFAIK, the datetime_parse function of AirTable can recognize only one format.
Is there a method to recognize multiple string formats as in the above examples?
Thanks.
+29DATETIME_PARSE is designed to turn a string into a date/time object, not a duration. In Airtable, a duration is a number of seconds with fancy formatting.
You can use a formula field to convert your string to a number of seconds and then set the field formatting options to ‘duration’. I do not know of an existing formula, and I am away from my computer so I cannot write it now. The formula that I envision would use a combination of REGEX_MATCH, REGEX_EXTRACT, VALUE, IF, and some math operators.
+3This use case interested me, so I wrote up the formula on my website.
I tried your code in your website.
It’s really great, but it concatenates the numbers instead of adding as if the numbers were string format.
Especially it adds zeros so that it becomes 6 digits.
I guess it’s not your mistake, but a bug of AirTable.
3h 12m becomes 7200010800 because 3h is 010800 seconds and 12m is 7200 seconds,
so 7200 + 010800 = 7200010800.
+29I tried your code in your website.
It’s really great, but it concatenates the numbers instead of adding as if the numbers were string format.
Especially it adds zeros so that it becomes 6 digits.
I guess it’s not your mistake, but a bug of AirTable.
3h 12m becomes 7200010800 because 3h is 010800 seconds and 12m is 7200 seconds,
so 7200 + 010800 = 7200010800.
Can you post screens capture of both your table and of the formula itself? There must be something else going on.
If you want to use different unit specifiers, you need to edit the formula to use the units in your text string. In your original post, you used “min” as the unit specifier for minutes, so I wrote the formula to look for “min”. However, in your most recent post, you are using “m” as the unit specifier. You can either edit the formula to look for only “m” or you can adjust your unit specifier in your text string.
Or maybe you did not get the entire formula. The formula includes SUM, and it doesn’t have any concatenation operators.
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.