Create duration from 3 integers

[Blair_Martin]

Has anyone else had this problem and if so how did you format it?

I have duration data in Airtable, specifically time we spent on a project that we will now bill our clients for. I am attempting to use zapier to send the information into QBO so we can bill faster. QBO requires the format to be hh:mm so that is what I set Airtable up to be, but when zapier pulls the data it is showing as an integer.

For example we worked 15 minutes, shown as 00:15 but in zapier it is shown as 900 (minutes*60). I tried to reformat it to hh:mm by using formatter by zapier and it returned 04:15 (four extra hours).

Not sure if this is an Airtable issue, a Zapier issue or a formatting issue. Any help would be greatly appreciated!

[W_Vann_Hall]

I’d try either

1. Creating a QBO-friendly field that turns your durations into appropriate hh:mm format and having Zapier pass that value to QBO.
2. Passing Zapier a duration value in minutes and then seeing if Formatter could correctly put that into hh:mm format.

If you go with Option 1, you can use this formula to convert durations into hh:mm-formatted text. (Obviously, replace {Duration} with the name of your duration field.)

IF(
    {Duration},
    INT(
        {Duration}/3600
        )&
      ':'&
      IF(
        MOD(
            {Duration}/60,
            60
            )<10,
        '0'
        )&
      MOD(
        {Duration}/60,
        60
        )
    )

[Blair_Martin]

I figured it out, thank you for the formula! It worked in Airtable but I then forgot to flip the data in Zapier! :woman_facepalming:

[Skyler_Proctor]

So this formula works for me, the only catch is I need to extend it to seconds, not just HH:MM. How would I modify this formula to have accurate HH:MM:SS?

[W_Vann_Hall]

I think this should do it — again, assuming {Duration} is a value given in seconds:

IF(
    {Duration},
    INT(
        {Duration}/3600
        )&
    ':'&
    IF(
        MOD(
            {Duration}/60,
            60
            )<10,
        '0'
        )&
    INT(
        MOD(
            {Duration}/60,
            60
            )
        )&
    ':'&
    IF(
        MOD(
            {Duration},
            60
            )<10,
        '0'
        )&
    MOD(
        {Duration},
        60
        )
    )