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

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