Discussions

Expand all | Collapse all

How to make this a numeric formula instead of duration

  • 1.  How to make this a numeric formula instead of duration

    Posted 30 days ago

    Hello,

    How can I make this a numeric formula instead  of a duration? 

    Min([Weekending Date]-[Employee - Departure / Check out],7)

    [Weekending Date]-[Employee - Departure / Check out} are both date fields 

    I want to show which ever is shorter in a numeric format. If I use days(7) I can get a duration of 7 days but I need to be able to add or subtract and multiply by this answer in other cells. 

    Thanks, 

    Tara



    ------------------------------
    Tara Stroup
    ------------------------------


  • 2.  RE: How to make this a numeric formula instead of duration

    Posted 30 days ago

    try this

    Min(ToDays([Weekending Date]-[Employee - Departure / Check out]),7)

    The bold part calculates the Duration, and then the ToDays function converts that span of time into a numeric number of days.  So you will need to change the field type now to formula numeric.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: How to make this a numeric formula instead of duration

    Posted 30 days ago

    That worked great. One more question. In the situation where the Employee Departure date is past the weekending date, because we have already planned the departure for the next week the formula returns a negative number. Is there a way to add if - then 7?

    Thanks, 

    Tara



    ------------------------------
    Tara Stroup
    ------------------------------



  • 4.  RE: How to make this a numeric formula instead of duration

    Posted 30 days ago

    Assuming that your stated logic is what you actually want, I like to use what are called formula variables.  That just makes the formula easier to read and to debug.  Keep in mind it may very well be you who has to look back at this formula a year from now and ask "what was I doing here?".

    var number RawDays = Min(ToDays([Weekending Date]-[Employee - Departure / Check out]),7);

    IF($RawDays < 0, 7, $RawDays)

    Here is a link to help on Formula variables.

    https://help.quickbase.com/user-assistance/formula_variables.html



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: How to make this a numeric formula instead of duration

    Posted 30 days ago

    That worked great. Thank you Mark!



    ------------------------------
    Tara Stroup
    ------------------------------