Convert a formula duration result into a numeric value

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
I am calculating duration based on timestamp values. I want to create a boolean return in another field if the value is over 14. The problem I have is that the ">" symbol will not work with duration values, and if I make the boolean field numeric it will not accept a value from the duration field it is referencing. Is there another way to say "greater than" using duration fields?
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
14 what? Minutes? Hours? Days? Weeks?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Please post your current formula.
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
The time is in hours. It is generated from a duration calculation based on timestamp:

[Stamp OUT]-[Stamp IN]

Which generates the duration in hours to the field [Period Duration]. That field is used in the following formula:

If([Period Duration]>14,"Yes",null)

Which then generates the error below:

The expression [Period Duration] on the left hand side of the operator ">" is of type duration while the expression 14 on the right hand side is of type number. The operator ">" cannot be used with these types of expressions.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Ok. No problem. Because it’s s duration, it needs to know the units.

If([Period Duration]>hours(14,)"Yes")
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
Perfect, Thanks!
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
After trying that it works as an argument, but it will not display the text "yes". It says expecting duration but found text. I can work with the result but it's not quite 100%. (I moved the comma outside the bracket as it didn't seem to like that)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
If you want the result to be Text, change the field type to formula Text.
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
I only want a text output if the duration is greater than 14 hours. When I try to use the > symbol in a text formula field I get a an error due to the > operator. Can't be applied on types text, duration.
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
actually I get the error either way although the formula works insofar as it returns a number from a duration which is what I really need. I can live without the text.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
I’m confused as to what result you are looking for. I think that you have never stated that in words.
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
I am hiding some fields based on the duration of a period. When the duration exceeds 14 hours I have a field with the title "Duty time over 14 hours?" This is the field where I want it to return a number based on duration and then say "yes" if that is true (this is the part where the formula works by returning a number, but will not print out the "yes" response). That also triggers the hidden fields to appear so more information can be entered.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Maybe what you want is to conver the duration to text and add “ Yes” if > 14 hours


ToText(Round(ToHours([Period Duration]),1))


& " Hours"


& If([Period Duration]>hours(14), " Yes")
(Edited)
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
That sounds like what I am trying to do, I'll give it a try.  Thanks for all of the help so far!
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
So close...It printed the "yes" text but also included 15 hours, so it looked like "15 hours yes". I pulled out the "& 'hours'" segment and now it says "15Yes". Is there a way to hide the 15? Also I don't understand what the 1 in the first line is for. Is that the number of days in the [period duration] field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
It does that because that is what I thought you wanted.

At this point I really have no idea what you would like the formula to do.

I think you better explain with some clear examples of the possible results you want to see for various examples of different Period Durations.
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
The field "Over 14 Hours?" is where the formula resides. I would like it to say Yes if period duration is over 14 hours. 

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
Right, that was my first response to your question.

If([Period Duration]>hours(14,)"Yes")
Photo of Dante Tognoli

Dante Tognoli

  • 448 Points 250 badge 2x thumb
Hmm...not sure how I got so off in the weeds on that. I think I got mixed up after changing the field type to formula-text. Anyway, it works now. Thanks for all of your help.