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
------------------------------
• 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
------------------------------
• 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
------------------------------
• 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
------------------------------
• 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
------------------------------