Forum Discussion

Qrew Captain

4 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...

Formulas and functions

MarkShnier__You
4 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

Qrew Legend

4 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
Qrew Captain
4 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
  Qrew Legend
  4 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
  ------------------------------
  - CarolMcconnell
    Qrew Captain
    4 years ago
    If the the record is not closed then it should calculate from the NWS Date Received to Todays date minus the number of pending days, else if the record is closed, it should calculate from the NWS Date Received to Date closed minus the pending days.
    
    I need it to calculate based on Work Days only Monday through Friday.
    
    ------------------------------
    Carol Mcconnell
    ------------------------------