Forum Discussion

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

Formulas and functions

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

CarolMcconnell

Qrew Captain

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

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

CarolMcconnell
Qrew Captain
3 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
------------------------------
- MarkShnier__You
  Qrew Legend
  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
  ------------------------------
  - CarolMcconnell
    Qrew Captain
    3 years ago
    Thanks for the help. What I did was just create a new field to calculate the end date and then added that to the formula.
    
    This is what I ended up with. WeekdaySub([End Date for NWS TAT],[NWS Date Received])-([Total pending First Open])+1
    
    I add the one because we count the start and end days also.
    
    ------------------------------
    Carol Mcconnell
    ------------------------------