Duration showing as Integer in Zapier

#1

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!

0 Likes

#2

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

#4

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:

0 Likes