Forum Discussion

CarolMcconnell's avatar
CarolMcconnell
Qrew Captain
3 years ago

Formula to find weekdays for a duration

I have this formula but I'm getting errors.  I'm not sure if it's just a syntax issue or something else.

If (IsNull ([Date Closed]),WeekdaySub(Today()-[NWS Date Received]-[Total pending First Open],[Date Closed]-[NWS Date Received]-[Total pending First Open]))

I have this in a Numeric Formula field.  Both the Date Closed and NWS Date Received are date fields but the Total Pending First Open is a Formula Duration Field.

As the formula is, I'm getting the error "Expecting Date but found duration"  on this section "WeekdaySub(Today()-[NWS Date Received]-[Total pending First Open]"

I appreciate any assistance.

------------------------------
Carol Mcconnell
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    3 years ago
    ok, try this
    // first calculate the End Date and put it into the formula variable called End Date
    var date EndDate = IF(IsNull ([Date Closed]),Today(), [Date Closed]); // ok so now we know the End Date

    WeekdaySub($EndDate, [NWS Date Received]) -    ToDays([Total pending First Open])

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    The concept of duration is a sense of elapsed time but that can be expressed in seconds minutes hours days weeks years. So I presume you want this to convert the Duration field to the numeric # of Days.

    If (IsNull ([Date Closed]),WeekdaySub(Today()-[NWS Date Received]-ToDays([Total pending First Open]),[Date Closed]-[NWS Date Received]-ToDays([Total pending First Open])))
    ​

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • CarolMcconnell's avatar
      CarolMcconnell
      Qrew Captain
      3 years ago
      Mark, thanks for that.  It is showing another issue now.

      If (IsNull ([Date Closed]),WeekdaySub(Today()-[NWS Date Received]-ToDays([Total pending First Open]),[Date Closed]-[NWS Date Received]-ToDays([Total pending First Open])))

      The operator '-' can't be applied on types duration or number.  

      It is only highlighting the first "-"

      ------------------------------
      Carol Mcconnell
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        Sorry I realize now that I don't really understand the logic of what you're trying to do. Can you say the formula in plain English words and then I can help you translate that to QuickBase syntax.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------