Forum Discussion

JeffreyJones1's avatar
JeffreyJones1
Qrew Trainee
6 years ago

Need to setup checkbox formula based on hours:minutes

I have a formula field that returns the hours:minutes of a technician onsite.  I want to use this field to create a checkbox formula that returns TRUE if the hours run over 4 hours and FALSE if it runs under 4 hours.  I keep getting errors regarding using the if function with a duration and I'm not sure how to get around this if at all possible.  Currently trying the syntax below but is not working.  Any help is appreciated.

If([day 1 time on site]>4,"TRUE","FALSE")

  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    6 years ago
    IF([field]>4,,)

    requires that [field] be numeric. You probably need to convert [day 1 time on site] (or the part of it that you want to evaluate) to numeric within this formula. Depending on the field type and format of [day 1 time on site] that may require a little bit of additional maneuvering.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Jeffery,
    If the field type being tested is Duration then you will need ot test against a duration.

    If([day 1 time on site] > Hours(4),"TRUE","FALSE")
    • JeffreyJones1's avatar
      JeffreyJones1
      Qrew Trainee
      6 years ago
      Thank you! This worked perfectly.  I was close, but have a lot to learn about coding.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Many new users get tripped up by formuals involving dates and time and durations, especially Durations.

      If you look back on your original formula, you will see that Quick base would have no way to know if you meant 4 minutes or 4 hours or 4 days or 4 weeks.

      So that is why we needed to convert that 4 to a Duration.
    • JeffreyJones1's avatar
      JeffreyJones1
      Qrew Trainee
      6 years ago
      Thanks again.  Yes I did see that syntax error, stating it wouldn't work due to the value being a duration, but I couldn't figure out how to get around it.  I found the Hours formula you listed, but wasn't using it correctly along with the If function.

      The sad part is I'm far from a new user of QB, as I've been using it close to ten years actually, but have only started getting into the coding portion of it the last couple years.  But lesson learned and I'll stop spending hours running in circles and ask experts like yourself when I'm stumped going forward.