Forum Discussion

CarolMcconnell's avatar
CarolMcconnell
Qrew Captain
3 years ago
Solved

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'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
    ------------------------------
CarolMcconnell's avatar
CarolMcconnell
Qrew Captain
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's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew 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's avatar
    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
    ------------------------------