Need to Convert a Duration Lookup Field to Numeric

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
I have a Duration Lookup field from another table, and I want to create a checkbox formula that says yes or no if the lookup field value is greater than zero. The ultimate goal is to have a checkbox that will limit a trigger in a Workflow Automation (only change the records where the box is checked).

All of this is a way to try and get around the limitation in Workflow Automations with duration fields.

Example of logic: IF ( [TSK - Est. Edit Time] > 0, TRUE)

Every fix I tried to apply told me I was using the wrong type of field.

Photo of Jennifer Peterson

Jennifer Peterson

  • 296 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
I would check if there is a conversion function for this.
Photo of Jennifer Peterson

Jennifer Peterson

  • 296 Points 250 badge 2x thumb
The conversions I tried (ToNumber, ToHours) still gave me errors saying it was the wrong value.
I am making an assumption here. But I think you get the idea. Assuming the duration field is a difference between two date fields, [From Date] and [End Date], I would have a formula checkbox in that table that says [From Date]=[End Date]. Then bring in that checkbox as a lookup field and that is the field you want. The logic here being that at the end of the day, the duration is the difference between two or more date or date-time fields and if you just want to see if the difference is zero, that means those two fields are equal. Does that help?
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
The corrected formula is this

IF([TSK - Est. Edit Time] > Hours(0), TRUE)

or you can also write is as just this


[TSK - Est. Edit Time] > Hours(0)


Mark, I thought of that, but the duration can be even a few minutes or seconds, or technically even milli seconds. So if the duration is less than 1 hour, and you are looking for the hours, I am not sure if it will show 0 or 0.x. You know what I mean?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
In fact, Zero seconds is equal to zero minutes is equal to zero hours is equal to zero days is equal to zero weeks.

Zero duration is zero duration no matter the Units.

So we can use Hours(0) or Weeks(0) or Seconds(0) to test against and the result would be the same.
(Edited)