Discussions

Expand all | Collapse all

Convert a formula duration result into a numeric value

QuickBaseCoach Dev./Training06-09-2018 01:02

QuickBaseCoach Dev./Training06-09-2018 01:02

Dante Tognoli06-09-2018 15:07

  • 1.  Convert a formula duration result into a numeric value

    Posted 06-09-2018 00:52
    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?


  • 2.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 01:02
    14 what? Minutes? Hours? Days? Weeks?


  • 3.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 01:02
    Please post your current formula.


  • 4.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 03:09
    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.


  • 5.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 04:21
    Ok. No problem. Because it�s s duration, it needs to know the units.

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


  • 6.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 15:07
    Perfect, Thanks!


  • 7.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 15:18
    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)


  • 8.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 17:28
    If you want the result to be Text, change the field type to formula Text.


  • 9.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 18:05
    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.


  • 10.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 18:10
    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.


  • 11.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 18:15
    I�m confused as to what result you are looking for. I think that you have never stated that in words.


  • 12.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 18:31
    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.


  • 13.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 19:15
    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")


  • 14.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 19:56
    That sounds like what I am trying to do, I'll give it a try.  Thanks for all of the help so far!


  • 15.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 20:08
    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?


  • 16.  RE: Convert a formula duration result into a numeric value

    Posted 06-09-2018 20:54
    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.


  • 17.  RE: Convert a formula duration result into a numeric value

    Posted 06-10-2018 02:26
    The field "Over 14 Hours?" is where the formula resides. I would like it to say Yes if period duration is over 14 hours. 



  • 18.  RE: Convert a formula duration result into a numeric value

    Posted 06-10-2018 02:38
    Right, that was my first response to your question.

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


  • 19.  RE: Convert a formula duration result into a numeric value

    Posted 06-10-2018 03:12
    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.