Help

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

Solved
Jump to Solution
1434 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Devan_Kreitzer
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions
Devan_Kreitzer
6 - Interface Innovator
6 - Interface Innovator

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:

See Solution in Thread

5 Replies 5

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
6 - Interface Innovator
6 - Interface Innovator

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:

@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.

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

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