Transforming a call Duration (number) into a datetime string to use Stacker

[Devan_Kreitzer]

Hello!

I am currently putting call data from Twilio into Airtable to then transform and present via Stacker. Currently, I can put the call start time (datetime), the call end time (datetime), and the duration (number) from Twilio itself.

I know there is the DATETIME_DIFF() formula, but the issue with this is that it produces an integer, where I am wanting h:mm:ss. I also know you can use the integrating formatting option that Airtable has on Formula fields, which works fine if I was only using Airtable. The difficulty comes in when I need to get this duration time into Stacker. Stacker does not do the same formatting option as Airtable and pulls in the duration integer (as seconds).

Any ideas?

[Justin_Barrett]

I appreciate the nod, @Devan_Kreitzer. However, you didn’t list the code in your reply, or a link to whatever other thread might have it. Please update with either one before this thread auto-closes in order to help other users who might have similar issues. Thanks!

[Devan_Kreitzer · Accepted solution]

REPOSTED TO PROVIDE BETTER SOLUTION:

Once again @Justin_Barrett saved the day on a different thread.

THE CODE BELOW IS FOR CONVERTING DURATION (number) to HH:MM:SS (string)

Here’s what I did to get the answer I needed:
Step 1) Make Formula field and use DATETIME_DIFF() on your two days to get the Duration.

Step 2) Ensure that you are using the internal Airtable formatting feature to display result as time. Here’s s info on to do this: https://support.airtable.com/hc/en-us/articles/360034843954-Calculate-the-difference-between-two-dat...

Step 3) Make new Formula column and use code below. Be sure to replace “Time” with your duration column.

IF(
    Time,
    FLOOR(Time / 3600) & ":" &
    RIGHT("0" & ROUNDDOWN(MOD(Time, 3600) / 60, 0), 2) & ":" &
    RIGHT("0" & ROUND(((MOD(Time, 3600) / 60) - ROUNDDOWN(MOD(Time, 3600) / 60, 0)) * 60, 0), 2)
)

Step 4) Now, apps like Stacker will be able to pull the string dates and not cause a completely metldown.

---

Previously mentioned thread where Justin answered my question provides other formulas for different time formats. Can be found here:

Convert Duration to string Product Suggestions

I have a duration field that is using the DATETIME_DIFF() function. These are for calls so I’d need to know the duration in hh:mm:ss. I know there is the built in formatting feature to change to a duration, but this duration field is being exported into a different program. If I wanted to change the created hh:mm:ss duration into a string, how would I do that?

[Justin_Barrett]

@Devan_Kreitzer For future reference, you don’t need to delete a post if you just want to make changes. The forum lets you edit your post for several days after it was made. Click the pencil icon below the post content to edit it.

[Devan_Kreitzer]

Thanks so much. Edit wasn’t available, so I deleted and remade the solution.

[Justin_Barrett]

Gotcha. You’re relatively new to the community, which could explain the lack of editing capability.