Skip to main content

Duration showing as Integer in Zapier

B

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!

    4 replies

    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • February 21, 2019

    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
        )
    )
    J
    1 person likes this
    • J
      Justin_Barrett
    B
    • Blair_Martin
    • Author
    • New Participant
    • 3 replies
    • February 22, 2019

    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:

    S
    W_Vann_Hall wrote:

    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
        )
    )

    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
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • July 31, 2019
    Skyler_Proctor wrote:

    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?


    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
        )
    )
    M
    1 person likes this
    • M
      Mykolas_Adamavi

    Reply


    Most helpful members this week

    Terms & Conditions