Request Open/Closes/Opened Again - Need to Track Total Days ACTIVE

Lyndsey Seskey

Hello! We monitor reports that begin date-tracking the day they're opened where a formula is currently in place to track from the initial date a request was submitted to the date the request was closed. Finding out how many days this request stayed open is simple. Issue is, there are certain times when a request is submitted, open for a while, then placed on hold for some weeks or months where its not active or actively being worked. Let's say it was open for 5 days before being placed on hold. Once this request is reactivated, I want to resume tracking number of days active from where it left off. This would essentially start the counter at the number of days the request was originally active (5) and then track days from there until the order is placed on hold again or closed out. In an extreme case, say the order was open 14 additional days and placed back on hold. This means that we would be at a total of 19 days active and would need to pick up where we left off when the request is made active again. How would one set this up?

Jeff Reisman

@Lyndsey Seskey

I think you'd need several helper columns and several Record a Date automation workflows.

Assuming that the request is added and is in Open status initially, we can use the system column "Created" as a starting point.

Create a "Hold Date" date type column. Create an automation rule:

Trigger: When a row is changed, when Status changes to "On Hold", run workflow when triggered.

Action: Record a Date in the Hold Date column.

Create a "Reopen Date" date type column. Create an automation rule:

Trigger: When a row is changed, when Status changes to "Reopened", run workflow when triggered.

Action: Record a Date in the Reopen Date column.

Create a "Closed Date" date type column. Create an automation rule:

Trigger: When a row is changed, when Status changes to "Closed", run workflow when triggered.

Action: Record a Date in the Closed Date column.

Now, in your Active Days column, we're going to use a mess of IFs and NETWORKDAYS. These will check if your date columns are blank or are date values, and calculate active days accordingly. NETWORKDAYS counts the number of workdays (Mon-Fri) between two dates.

=IF(AND(ISBLANK([Hold Date]@row), ISBLANK([Reopen Date]@row), ISBLANK([Closed Date]@row)), NETWORKDAYS(Created@row, TODAY()), IF(AND(ISBLANK([Hold Date]@row), ISBLANK([Reopen Date]@row), ISDATE([Closed Date]@row)), NETWORKDAYS(Created@row, [Closed Date]@row), IF(AND(ISDATE([Hold Date]@row), ISBLANK([Reopen Date]@row), ISBLANK([Closed Date]@row)), NETWORKDAYS(Created@row, [Hold Date]@row), IF(AND(ISDATE([Hold Date]@row), ISDATE([Reopen Date]@row), ISBLANK([Closed Date]@row)), (NETWORKDAYS(Created@row, [Hold Date]@row) + NETWORKDAYS([Reopen Date]@row, TODAY())), IF(AND(ISDATE([Hold Date]@row), ISDATE([Reopen Date]@row), ISDATE([Closed Date]@row)), (NETWORKDAYS(Created@row, [Hold Date]@row) + NETWORKDAYS([Reopen Date]@row, [Closed Date]@row)), "")

Check your parentheses color-coding on these to be sure I didn't miss any. I would recommend trying the individual IFs out one at a time, matching or not matching your criteria, to make sure they all do what you want.

The criteria and results, in a list:

Hold, reopen, and closed are blank: count from created date until today.

Hold and Reopen are blank, closed date is a date, count from created date until the closed date.

Hold date is a date, Reopen and Closed dates are blank, count from created date to Hold date.

Hold and Reopen are dates, count from created to hold, plus count from reopen date to today.

If hold, reopen, and closed are all dates, count from created to hold, plus from reopen to closed.