Convert a formula duration result into a numeric value

  • 0
  • 1
  • Question
  • Updated 1 year 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

  • 682 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
14 what? Minutes? Hours? Days? Weeks?
Please post your current formula.
Photo of Dante Tognoli

Dante Tognoli

  • 682 Points 500 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.
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

  • 682 Points 500 badge 2x thumb
Perfect, Thanks!
Photo of Dante Tognoli

Dante Tognoli

  • 682 Points 500 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)
If you want the result to be Text, change the field type to formula Text.
Photo of Dante Tognoli

Dante Tognoli

  • 682 Points 500 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

  • 682 Points 500 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.
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

  • 682 Points 500 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.
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

  • 682 Points 500 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

  • 682 Points 500 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?
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

  • 682 Points 500 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. 

Right, that was my first response to your question.

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

Dante Tognoli

  • 682 Points 500 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.